PostgreSQL basics
Introduction
PostgreSQL (pronounced “Post-Gres-Q-L”)
— a free object-relational database management system (DBMS).
It exists in implementations for many
UNIX
-like platforms, including AIX,
various BSD systems, HP-UX, IRIX,
Linux
, macOS, Solaris/OpenSolaris,
Tru64, QNX, as well as for
Microsoft Windows
.
Checking the version
After installation, check the version of the installed PostgreSQL
postgres -V
postgres (PostgreSQL) 9.2.24
or
psql -V
psql (PostgreSQL) 13.7
Checking the status
PostgreSQL 9.6
systemctl status -l postgresql
Instead of 11 specify your version
systemctl status -l postgresql-11
The variants inactive, failed, active are possible
● postgresql-11.service - PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: inactive (dead) Docs: https://www.postgresql.org/docs/11/static/
● postgresql-11.service - PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: failed (Result: exit-code) since Tue 2022-09-20 12:01:41 EEST; 6s ago Docs: https://www.postgresql.org/docs/11/static/ Process: 12952 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE) Nov 17 21:15:52 host.andrei.com systemd[1]: Starting PostgreSQL 11 database server... Nov 17 21:15:52 host.andrei.com systemd[1]: postgresql-11.service: control process exited, code=exited status=1 Nov 17 21:15:52 host.andrei.com systemd[1]: Failed to start PostgreSQL 11 database server. Nov 17 21:15:52 host.andrei.com systemd[1]: Unit postgresql-11.service entered failed state. Nov 17 21:15:52 host.andrei.com systemd[1]: postgresql-11.service failed.
● postgresql-11.service - PostgreSQL 11 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled) Active: active (running) since Tue 2022-09-20 12:19:45 EEST; 7s ago Docs: https://www.postgresql.org/docs/11/static/ Process: 15899 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 15904 (postmaster) Tasks: 8 CGroup: /system.slice/postgresql-11.service ├─15904 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/ ├─15906 postgres: logger ├─15908 postgres: checkpointer ├─15909 postgres: background writer ├─15910 postgres: walwriter ├─15911 postgres: autovacuum launcher ├─15912 postgres: stats collector └─15913 postgres: logical replication launcher Nov 17 21:15:52 host.andrei.com systemd[1]: Starting PostgreSQL 11 database server... Nov 17 21:15:52 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.397 EEST [15904] LOG: listening on IPv6 address "::1", port 5432 Nov 17 21:15:52 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.397 EEST [15904] LOG: listening on IPv4 address "127.0.0.1", port 5432 Nov 17 21:15:52 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.401 EEST [15904] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" Nov 17 21:15:52 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.405 EEST [15904] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" Nov 17 21:15:52 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.414 EEST [15904] LOG: redirecting log output to logging collector process Nov 17 21:15:52 host.andrei.com postmaster[15904]: 2022-09-20 12:19:45.414 EEST [15904] HINT: Future log output will appear in directory "log". Nov 17 21:15:52 host.andrei.com systemd[1]: Started PostgreSQL 11 database server.
Restarting PostgreSQL
For version 9
systemctl restart postgresql
For more modern versions, for example 11
systemctl restart postgresql-11
Entering Postgres
When postgresql-11 has the active status you can enter postgres by running
sudo su - postgres
Last login: Tue Nov 17 21:15:52 EEST 2026 on pts/0 -bash-4.2$
psql
psql (11.17) Type "help" for help. postgres=#
After installation
The location of the files with the settings, for example, postgresql.conf can be obtained by running
-bash-4.2$ su - postgres -c "psql -c 'SHOW config_file;'"
In PostgreSQL 9 the result will be like this:
Password: config_file ------------------------------------- /var/lib/pgsql/data/postgresql.conf (1 row)
In more modern versions the result will contain the version number
---------------------------------------- /var/lib/pgsql/11/data/postgresql.conf (1 row)
Let's study the contents
/var/lib/pgsql/data
ll /var/lib/pgsql/data/
total 48 drwx------. 7 postgres postgres 67 Jun 9 22:54 base drwx------. 2 postgres postgres 4096 Jun 9 23:19 global drwx------. 2 postgres postgres 18 Jun 9 13:54 pg_clog -rw-------. 1 postgres postgres 4371 Jun 10 01:23 pg_hba.conf -rw-------. 1 postgres postgres 1636 Jun 9 13:54 pg_ident.conf drwx------. 2 postgres postgres 58 Jun 10 00:00 pg_log drwx------. 4 postgres postgres 36 Jun 9 13:54 pg_multixact drwx------. 2 postgres postgres 18 Jun 9 14:14 pg_notify drwx------. 2 postgres postgres 6 Jun 9 13:54 pg_serial drwx------. 2 postgres postgres 6 Jun 9 13:54 pg_snapshots drwx------. 2 postgres postgres 25 Jun 10 02:06 pg_stat_tmp drwx------. 2 postgres postgres 18 Jun 9 13:54 pg_subtrans drwx------. 2 postgres postgres 6 Jun 9 13:54 pg_tblspc drwx------. 2 postgres postgres 6 Jun 9 13:54 pg_twophase -rw-------. 1 postgres postgres 4 Jun 9 13:54 PG_VERSION drwx------. 3 postgres postgres 60 Jun 9 13:54 pg_xlog -rw-------. 1 postgres postgres 19889 Jun 10 01:43 postgresql.conf -rw-------. 1 postgres postgres 45 Jun 9 14:14 postmaster.opts -rw-------. 1 postgres postgres 92 Jun 9 14:14 postmaster.pid
Connecting to a DB
To gain access to the databases you can use the standard psql console.
Windows:
If you want to connect from
bash on Windows
do not forget to add the location of
psql.exe
(in my case C:\Program Files\PostgreSQL\12\bin) to PATH
How this is done is described in the article
PATH
psql.exe -h localhost -p 5432 -U postgres
Linux:
sudo su - postgres
psql
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1)) Type "help" for help.
If the DB is on the local host
psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME
Get data about the connection
\conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
List of existing DBs
The list of databases already existing on the server can be obtained with the command
\l
Name | Owner | Encoding | Collate | Ctype | Access privileges --------------+----------+----------+----------------------------+----------------------------+----------------------- eth1.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | topbicyle.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (6 rows)
Pay attention to the Encoding, Collate and Ctype columns. Knowledge of encodings may come in handy for you in the future.
Create a database
Let's create a database heihei_ru_db with the utf8 encoding
CREATE DATABASE "heihei_ru_db" WITH OWNER "postgres" ENCODING 'UTF8';
CREATE DATABASE
Let's create a database heihei with the utf8 encoding and specify values for Collate, Ctype and Template
CREATE DATABASE "heihei" WITH OWNER "postgres" ENCODING
'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0;
CREATE DATABASE
Let's check what we got
\l
Name | Owner | Encoding | Collate | Ctype | Access privileges --------------+----------+----------+----------------------------+----------------------------+----------------------- heihei | postgres | UTF8 | C | C | eth1.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | topbicyle.ru | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | English_United States.1252 | English_United States.1252 | (7 rows)
If in bash I run
locale -a
Then I will get four available encodings at once
C
C.UTF-8
en_US.utf8
POSIX
But I cannot create a DB, for example, with en_US.utf8
CREATE DATABASE "heihei" WITH OWNER "postgres" ENCODING 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8' TEMPLATE = template0;
It leads to an error
ERROR: invalid locale name: "en_US.UTF-8"
If you know how to solve this problem - write in the comments to the article.
UPD: I ran the same command in Ubuntu and the database was created successfully
CREATE DATABASE "new_db" WITH OWNER "postgres" ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE = template0;
CREATE DATABASE
Delete a database
To delete a database use the command
DROP DATABASE db_name;
DROP DATABASE
Check the encodings
You can check the server encoding with the command
SHOW SERVER_ENCODING;
server_encoding ----------------- UTF8 (1 row)
Check the client encoding
SHOW CLIENT_ENCODING;
client_encoding ----------------- WIN1252 (1 row)
When you are in the interactive mode of PostgreSQL a prefix appears in the console
db=>
Where db is the name of the current database
Show the address of the current directory
\!
[andrei@localhost ~]$
You can return to PostgreSQL by running
exit
db=>
Enter a DB
To start working with a database you need to know its name, for example
suppose you need the database that was simply named HeiHei_ru_DB
Use the command
\c HeiHei_ru_DB
You are now connected to database "HeiHei_ru_DB" as user "postgres".
If you work in Linux and the database is on the same host you can run
psql -h 127.0.0.1 -d DATABASENAME -U DATABASEUSERNAME
To see the list of tables enter
\dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | person | table | postgres
(1 row)
To view the whole table person you can already use the standard
SELECT * FROM person;
List of active connections to the DB
You can study the connections to the DB by running
SELECT * FROM pg_stat_activity WHERE datname = 'DB_NAME';
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_star t | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type -------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+---------- --+-------------+--------------+-----------------+------------+-------+-------------+--------------+-------+-------------- (0 rows)
Rename a DB
ALTER DATABASE old_name RENAME TO new_name;
Details:
The list of existing DBs can be obtained with the command \l
\l
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- db01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)
If you need to rename the DB db01, the first thing to do is to exit it.
To do this you can enter another DB, for example, postgres.
You can study the connections to the DB by running
SELECT * FROM pg_stat_activity WHERE datname = 'db01';
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_star t | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type -------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+---------- --+-------------+--------------+-----------------+------------+-------+-------------+--------------+-------+-------------- (0 rows)
All current connections can be closed with the command
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = DB_NAME;
The renaming itself is performed with the command
ALTER DATABASE db01 RENAME TO db02;
ALTER DATABASE
\l
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- db02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | … (4 rows)
If someone is connected to the database. For example, you yourself from another terminal
\c db02
You are now connected to database "db02" as user "postgres".
postgres=# SELECT datname, pid, backend_start, state FROM pg_stat_activity WHERE datname = 'db02';
datname | pid | backend_start | state ---------+-------+-------------------------------+------- db02 | 21238 | 2026-11-17 21:15:52.503073+03 | idle (1 row)
All connections can be closed by running
postgres=# SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = 'db02';
pg_terminate_backend ---------------------- t (1 row)
Get a value from a DB
Example - let's write a value from a database to a file using echo
echo $(psql -h localhost -d db_name -U db_user -c "SELECT host_certificate FROM hosts WHERE (addresses='192.168.56.200');") > raw_cert
Database contents
In the previous paragraph we created the booking_sites table in the heihei database
Let's make sure that the script worked successfully
Let's switch to the heihei database and check whether the table was created
\c heihei
You are now connected to database "heihei" as user "postgres".
heihei=# \dt
List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | booking_sites | table | postgres (1 row)
Describing a table
If the table was created some time ago. You may have already forgotten which exact columns it contains.
The \d command is used to describe a table
\d booking_sites
Table "public.booking_sites" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+------------------------------------------- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(50) | | | Indexes: "booking_sites_pkey" PRIMARY KEY, btree (id)
UPDATE: Changing a value
Let's change the expiry date of the certificate in the table certificates
Let's study the current situation
aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070');
issuer | not_after --------------------------------------------------------------------------------------------+------------------------ CN=192.168.56.174,OU=Andrei CA 3bbce96c-b117-46l0-65a7-f3c04c9f591b | 2027-07-19 16:39:59+03 (1 row)
Let's update the value
aodb=> UPDATE certificates SET not_after='2022-07-22 16:39:59+03' WHERE (serial='2402020070');
UPDATE 1
Let's check the result
aodb=> SELECT issuer, not_after FROM certificates WHERE (serial='2402020070263697801');
issuer | not_after --------------------------------------------------------------------------------------------+------------------------ CN=192.168.56.174,OU=Andrei CA 3bbce96c-b117-46l0-65a7-f3c04c9f591b | 2022-07-22 16:39:59+03 (1 row)
Changing a table
When we need to update the name of a table column we use the ALTER command
Suppose the website_url field does not have enough room for 50 characters. Let's increase the length to 60.
ALTER TABLE booking_sites ALTER column website_url TYPE VARCHAR(60);
Let's check whether the table has changed
\d booking_sites
Table "public.booking_sites" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+------------------------------------------- id | bigint | | not null | nextval('booking_sites_id_seq'::regclass) company_name | character varying(50) | | not null | origin_country | character varying(50) | | not null | age | character varying(3) | | not null | date_of_birth | date | | not null | website_url | character varying(60) | | | Indexes: "booking_sites_pkey" PRIMARY KEY, btree (id)
Exit the console
\q
Find out the current schema
SELECT current_schema();
current_schema ---------------- public (1 row)
Environment variables
$PGDATA - the address of the data directory
echo $PGDATA
/var/lib/pgsql/data
Allow external connections
For PostgreSQL 9
vi /var/lib/pgsql/data/postgresql.conf
For more modern versions of PostgreSQL you need to specify the directory, for example 11
vi /var/lib/pgsql/11/data/postgresql.conf
# - Connection Settings - # listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) listen_addresses = '*' port = 5432 # (change requires restart)
vi /var/lib/pgsql/data/pg_hba.conf
Or
vi /var/lib/pgsql/11/data/pg_hba.conf
host all all 0.0.0.0/0 md5
Enable SSL
openssl genrsa -des3 -out server.key 1024
openssl rsa -in server.key -out server.key
chmod 400 server.key
chown postgres.postgres server.key
openssl req -new -key server.key -days 3650 -out server.crt -x509
cp server.crt root.crt
vi /var/lib/pgsql/11/data/postgresql.conf
# - SSL - ssl = on #ssl_ca_file = '' ssl_cert_file = '/var/lib/pgsql/11/data/server.crt' #ssl_crl_file = '' ssl_key_file = '/var/lib/pgsql/11/data/server.key'
Article author: Andrei Olegovich