Editing PostgreSQL

[[wikipedia:PostgreSQL]], often simply '''Postgres''', is an [[object-relational database management system]] (ORDBMS). PostgreSQL is [[ACID]]-compliant and [[Transactional]]. PostgreSQL has updatable [[Views]] and [[Materialized view]], [[Triggers]], [[Foreign key]]; supports functions and stored procedures, and other expandability. PostgreSQL listens by default in TCP port 5432.

== Installing PostgreSQL ==
=== MacOS ===
* <code>[[brew install postgres]]</code>: <code>[[pg_ctl]]</code> and other 33 binaries.
* <code>[[brew install libpq]]</code>: <code>[[psql]]</code> and other 28 binaries.

=== Linux, BSD variants ===
The easiest way to install PostgreSQL in these operating systems is to use their respective built-in package managers - for instance, the <code>pkg</code> command in DragonFlyBSD or <code>apt</code> in Debian and its derivatives. Be advised that there will be different variants of PostgreSQL available for install at a given point, specially regarding version numbers and client/server side. You should decide beforehand which version suits your needs for a particular application. Furthermore, you need to make sure that you have sufficient privileges in the system to install software (properly configured sudo/doas access, for example) or your install will fail.
* [[Ubuntu]]: <code>[[apt install postgresql]]</code>
* [[Amazon AMI]]: <code>[[apt install postgresql-server]]</code>

=== Docker ===
 [[docker run]] -p 5432:5432 --name MY_POSTGRES_DB -e POSTGRES_PASSWORD=mysecretpassword -d postgres<ref>https://hub.docker.com/_/postgres</ref>
 * [[Docker-compose.xml PostgreSQL]]

 docker run -p 5432:5432 -d \
    --name some-postgres \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    -v /custom/mount:/var/lib/postgresql/data \
    postgres

== PostgreSQL ==
 [[bitnami/postgresql]]
 [[bitnami/postgresql-ha]]

== Configuration and data files ==
* <code>/etc/postgresql/10/main/[[postgresql.conf]]</code>
* <code>/etc/postgresql/10/main/[[pg_hba.conf]]</code> (PostgreSQL Client Authentication Configuration File)

 # "local" is for Unix domain socket connections only
 local   all             all                                     md5

Data:
* <code>[[/var/lib/postgresql/]]</code>

== [[PostgreSQL logs]] ==
*<code>[[/var/log/]]postgresql</code>

== Basic Commands==
Binaries: <code>[[psql]]</code>, <code>[[pg_dump]]</code>.

* Connect to a PostgreSQL database: <code>[[psql]] -h localhost -U postgres -d somedb</code><ref>http://postgresguide.com/utilities/psql.html</ref>
* Connect to a PostgreSQL database with a timeout of 5 seconds: <code>env PGCONNECT_TIMEOUT=5 psql -h localhost -U postgres -d somedb</code><ref>http://postgresguide.com/utilities/psql.html</ref>
* [[Show databases]]:
** <code>[[\list]]</code>
** <code>SELECT datname FROM pg_database WHERE datistemplate = false;</code>
* Show tables: <code>\dt</code> or <code>SELECT * FROM [[pg_catalog]].pg_tables;</code><ref>https://stackoverflow.com/questions/769683/show-tables-in-postgresql</ref>
* [[Show users]]: <code>[[\du]]</code>
* Drop DB: <code>[[dropdb]] DB_NAME</code>

* Describe ''employees'' table: <code>\d employees</code>
* Show/List [[schema]]s: <code>select [[nspname]] from [[pg_catalog]].pg_namespace;</code><ref>https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql</ref>
* Show version: <code>[[SELECT version();]]</code>

* Create backup: use <code>pg_dump</code> <ref>https://www.postgresql.org/docs/current/static/app-pgdump.html</ref> and <code>~/.pgpass</code> file for automating login.
* Create user: 
** <code>[[create user]] <username></code>
** ALTER USER user_name WITH PASSWORD 'new_password';
* <code>[[create database]]</code>
* Modify user privileges:
** Privileges to create DB: <code>ALTER USER user1 CREATEDB;</code>


* <code>systemctl [ [[systemctl status postgresql|status]] | [[systemctl stop postgresql|stop]] | [[systemctl start postgresql|start]] ] postgresql  </code>


* <code>[[\l]], [[\du]], [[\dt]], [[\c]], [[\di]]</code>

== Processs ==
* [[wal]] writer process

== Related terms ==
* [[Role]], [[Schema]]
* <code>[[pg_upgradecluster]]</code>
* [[Adminer]]
* [[Amazon Redshift]]
* [[PostgreSQL monitoring]]
** [[PostgreSQL statistics collector]]: <code>[[pg_stat]]*</code>
** <code>[[prometheus-postgres-exporter]]</code>
* [[Multi-Version Concurrency Control (MVCC)]]
* [[Index (PostgreSQL)]]
* [[Greenplum]]
* [[PostgreSQL replication]]
* [[PGO PostgreSQL Operator]]
* [[Amazon Aurora PostgreSQL Serverless]]
* [[PgBouncer]] connection pooler
* [[Amazon RDS for PostgreSQL]]
* [[Master]] user for RDS: <code>[[postgres]]</code>

== Activities ==
* [[PostgreSQL parameter tunning]]
* Read PostgreSQL - How to grant access to users? https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html

== See also ==
* [[Patroni]] [[HA]] solution for PostgreSQL that requires <code>[[etcd]]</code>, <code>[[Zookeeper]]</code>, or <code>[[Consul]]</code>
* {{PostgreSQL extensions}}
* {{PostgreSQL}}
* {{DBs}}

[[Category:PostgreSQL]]
[[Category:Databases]]

{{CC license}}
Source: wikiversity