PostgreSQL

Updated 14 April 2021

PostgreSQL

PostgreSQL is a free object-relational database system.

Installation

Install PostgreSQL:

emerge -a dev-db/postgresql

Setup PostgresSQL:

emerge --config postgresql
Configuring pkg...

 * You can modify the paths and options passed to initdb by editing:
 *     /etc/conf.d/postgresql-13
 * 
 * Information on options that can be passed to initdb are found at:
 *     https://www.postgresql.org/docs/13/static/creating-cluster.html
 *     https://www.postgresql.org/docs/13/static/app-initdb.html
 * 
 * PG_INITDB_OPTS is currently set to:
 *     --encoding=UTF8
 * 
 * Configuration files will be installed to:
 *     /etc/postgresql-13/
 * 
 * The database cluster will be created in:
 *     /var/lib/postgresql/13/data
 * 
 * Continuing initialization in 5 seconds (Control-C to cancel) ...
 [ ok ]
 * Creating the data directory ...
 * Initializing the database ...
could not change directory to "/var/calculate/tmp/portage/dev-db/postgresql-13.2/homedir": Отказано в доступе
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  ru_RU.utf8
  CTYPE:    ru_RU.utf8
  MESSAGES: ru_RU.utf8
  MONETARY: ru_RU.utf8
  NUMERIC:  C
  TIME:     ru_RU.utf8
The default text search configuration will be set to "russian".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/13/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... W-SU
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/lib64/postgresql-13/bin/pg_ctl -D /var/lib/postgresql/13/data -l logfile start

 * The autovacuum function, which was in contrib, has been moved to the main
 * PostgreSQL functions starting with 8.1, and starting with 8.4 is now enabled
 * by default. You can disable it in the cluster's:
 *     /etc/postgresql-13/postgresql.conf
 * 
 * The PostgreSQL server, by default, will log events to:
 *     /var/lib/postgresql/13/data/postmaster.log
 * 
 * You should use the '/etc/init.d/postgresql-13' script to run PostgreSQL
 * instead of 'pg_ctl'.
 * Конфигурация утилитами Calculate системных настроек postgresql ...
 * Утилиты Calculate изменили файлы:
 *      /var/lib/portage/world

The output above contains the main information.

Start PostgreSQL:

/etc/init.d/postgresql-13 start
postgresql-13 | * /run/postgresql: creating directory
postgresql-13 | * /run/postgresql: correcting owner
postgresql-13 | * Starting PostgreSQL 13 ...                                 [ ok ]

Add the SQL server to autostart:

rc-update add postgresql-13
 * service postgresql-13 added to runlevel default

Configuration with password access

Postgres user configuration

Set a password for the postgres user:

psql -U postgres

psql (13.2)
Введите "help", чтобы получить справку.
postgres=# \password
Enter new password:
Enter it again:
postgres=# \q

Database access configuration

Configure access to the SQL server by allowing only local queries and those from the local network ~192.168.0.0/24~:

/etc/postgresql-13/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
host    all             all             192.168.0.0/24          password
# IPv6 local connections:
#host   all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local  replication     all                                     trust
#host   replication     all             127.0.0.1/32            trust
#host   replication     all             ::1/128                 trust

Specify IP addresses to be listened to by the PostgreSQL, comma-separated:

/etc/postgresql-13/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '192.168.0.1,127.0.0.1' # what IP address(es) to listen on;
                                           # comma-separated list of addresses;
                                           # defaults to 'localhost'; use '*' for all
                                           # (change requires restart)

Note

If the server needs to be accessed from all network interfaces, set "*" instead of IPs.

Restart the database server:

/etc/init.d/postgresql-13 restart
postgresql-13 | * Stopping PostgreSQL 13 (this can take up to 92 seconds) ... [ ok ]
postgresql-13 | * /run/postgresql: correcting mode
postgresql-13 | * Starting PostgreSQL 13 ...                                  [ ok ]

Check for operating ports:

netstat -an
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     
tcp        0      0 192.168.0.1:5432        0.0.0.0:*               LISTEN     
tcp6       0      0 :::22                   :::*                    LISTEN     
udp        0      0 127.0.0.1:41006         127.0.0.1:41006         ESTABLISHED
Active UNIX domain sockets (servers and established)
Proto RefCnt Flags       Type       State         I-Node   Path
unix  2      [ ACC ]     STREAM     LISTENING     281718   /run/postgresql/.s.PGSQL.5432

Exampe: create a database and a database user

Let's create a database, called dbtest and a user, called test to work with it:

psql -U postgres

Пароль пользователя postgres: 
psql (13.2)
Введите "help", чтобы получить справку.

postgres=# create database dbtest;
CREATE DATABASE
postgres=# create role test with login;
CREATE ROLE
postgres=# \password test
Введите новый пароль: 
Повторите его: 
postgres=# grant connect, create on database dbtest to test;
GRANT
postgres=# \l dbtest
                                 Список баз данных
    Имя    | Владелец | Кодировка | LC_COLLATE |  LC_CTYPE  |     Права доступа     
-----------+----------+-----------+------------+------------+-----------------------
 dbtest    | postgres | UTF8      | ru_RU.utf8 | ru_RU.utf8 | =Tc/postgres         +
           |          |           |            |            | postgres=CTc/postgres+
           |          |           |            |            | test=Cc/postgres
(1 строка)

postgres=# \q

The database and the database user have been created.

You can now check the user:

psql -U test -d dbtest
Пароль пользователя test:
psql (13.2)
Введите "help", чтобы получить справку.

test=> \q

Configuration with peer authorization

Configuring authorization

Configure peer authorization for local connections only:

/etc/postgresql-13/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
#host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
#host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     trust
#host    replication     all             127.0.0.1/32            trust
#host    replication     all             ::1/128                 trust

Restart PostgreSQL:

/etc/init.d/postgresql-13 restart

Creating users

Create a system test user:

useradd test

Create a Postgresql test user with the permission to create new databases:

su postgres -c psql
psql (13.2)
Type "help" for help.

postgres=# CREATE USER test CREATEDB;
CREATE ROLE
postgres=# \q

Creating database

Create a testdb+ database as the test user:

su test -c 'psql -d postgres'
psql (13.2)
Type "help" for help.

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# \q