PostgreSQL
Updated 14 April 2021
Contents
PostgreSQL is a free object-relational database system.
Installation
Install PostgreSQL:
emerge -a dev-db/postgresql
Setup PostgresSQL:
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:
postgresql-13 | * /run/postgresql: creating directory postgresql-13 | * /run/postgresql: correcting owner postgresql-13 | * Starting PostgreSQL 13 ... [ ok ]
Add the SQL server to autostart:
* service postgresql-13 added to runlevel defaultConfiguration 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:
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:
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:
Пароль пользователя test: psql (13.2) Введите "help", чтобы получить справку. test=> \q
Configuration with peer authorization
Configuring authorization
Configure peer authorization for local connections only:
# 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:
Creating users
Create a system test user:
useradd test
Create a Postgresql test user with the permission to create new databases:
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:
psql (13.2) Type "help" for help. postgres=# CREATE DATABASE testdb; CREATE DATABASE postgres=# \q