This document is a WORK IN PROGRESS.
This is just a quick personal cheat sheet: treat its contents with caution!

PostgreSQL¶

PostgreSQL is a free and open source RDBMS.

Reference(s)

Table of contents¶

Install
Config
Use
- Major update procedure
- Minor update procedure

Install¶

emergepacmanaptyumdnf

# emerge -a dev-db/postgresql
# emerge --config dev-db/postgresql

TODO

# pacman -S postgresql

TODO

# apt install postgresql

TODO

# yum install postgresql

dnf install postgresql

Config¶

Main configuration files of PostgreSQL are located in /etc/postgresql-x.x/.

Sample config files are available in /usr/share/postgresql-x.x/.

Allow local identification to the postgres user without password in order to set one later on:

# vi /etc/postgresql-x.x/pg_hba.conf
    > ...
    > # TYPE  DATABASE        USER            ADDRESS                 METHOD
    > # "local" is for Unix domain socket connections only
  ~ > local   all             all                                     trust
    > # IPv4 local connections:
    > host    all             all             127.0.0.1/32            password
    > # IPv6 local connections:
    > host    all             all             ::1/128                 password
    > # Allow replication connections from localhost, by a user with the
    > # replication privilege.
  ~ > local   replication     all                                     trust
    > host    replication     all             127.0.0.1/32            password
    > host    replication     all             ::1/128                 password

Start the PostgreSQL service:

OpenRCRunitSysVinitSystemD

# /etc/init.d/postgresql-x.x start

# sv up postgresql-x.x

# service postgresql-x.x start

# systemctl start postgresql-x.x

Define the postgres password:

# psql -U postgres
    > \password
    > ...
    > \q

Forbid local identification without password:

# vi /etc/postgresql-x.x/pg_hba.conf
    > ...
    > # TYPE  DATABASE        USER            ADDRESS                 METHOD
    > # "local" is for Unix domain socket connections only
  ~ > local   all             all                                     password
    > # IPv4 local connections:
    > host    all             all             127.0.0.1/32            password
    > # IPv6 local connections:
    > host    all             all             ::1/128                 password
    > # Allow replication connections from localhost, by a user with the
    > # replication privilege.
  ~ > local   replication     all                                     password
    > host    replication     all             127.0.0.1/32            password
    > host    replication     all             ::1/128                 password

Restart the PostgreSQL service and add it to the init system:

OpenRCRunitSysVinitSystemD

# /etc/init.d/postgresql-x.x restart
# rc-update add postgresql-x.x default

# sv restart postgresql-x.x
# ln -s /etc/runit/sv/postgresql-x.x /run/runit/service/

# service postgresql-x.x restart
# chkconfig postgresql-x.x on

# systemctl restart postgresql-x.x
# systemctl enable postgresql-x.x

Use¶

Use the -d option to connect to the database you created (without specifying a database, psql will try to access a database that matches your username).
```
$ psql -d myDatabaseName
```
Use the -U option to connect to a user (e.g. postgres):
```
$ psql -U postgres
```
psql command to get help:
```
=> \help
```
psql command to connect to a particular database:
```
=> \c <database>
```
psql command to list all users and their permission levels:
```
=> \du
```
psql command to show summary information about all tables in the current database:
```
=> \dt
```
psql command to exit/quit the psql shell:
```
=> \q # or CTRL+d
```
There are of course many more meta commands. To see all psql meta commands run:
```
=> \?
```

Major update procedure¶

For major versions only, e.g. from PostgreSQL 11 to 12:

E.g.:

$ pg_upgrade -U postgres \
> -d /var/lib/postgresql/11/data -D /var/lib/postgresql/12/data \
> -b /usr/lib64/postgresql-11/bin -B /usr/lib64/postgresql-12/bin

After an update, don't forget to add the new PostgreSQL version to the init system, and remove the old one:

OpenRCRunitSysVinitSystemD

# rc-update add postgresql-y.y.new default
# rc-update del postgresql-x.x.old default

# ln -s /etc/runit/sv/postgresql-y.y.new /run/runit/service/
# unlink /run/runit/service/postgresql-x.x.old

# chkconfig postgresql-y.y.new on
# chkconfig postgresql-x.x.old off

# systemctl enable postgresql-y.y.new
# systemctl disable postgresql-x.x.old

Check that your data bases are present:

$ sudo -u postgres psql -c "\list"

If a database is missing, you can recover it (e.g. a Nextcloud database) this way:

Gentoo

# rc-service postgresql-12 stop
# rc-service postgresql-11 start
# eselect postgresql set 11
$ sudo -u postgres psql --version
$ cd /path/to/backup
$ sudo -u postgres pg_dump nextcloud | sudo tee pg_dump_`date +"%Y%m%d"`

# rc-service postgresql-11 stop
# rc-service postgresql-12 start
# eselect postgresql set 12
$ sudo -u postgres psql --version
$ sudo -u postgres psql nextcloud < pg_dump_20200314

Now the old version of PostgreSQL can safely be removed from the system:

Minor update procedure¶

TODO

If this cheat sheet has been useful to you, then please consider leaving a star here.