Skip to content

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

# 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:

# /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:

# /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:

# 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:

# 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.