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

Banner image

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

Banner image