Postgresql: Command runbook for DBA

Postgresql runbook for a DBA:

Steps to perform "cluster" command in postgres.... after brief data collection

Ensure to enable putty log...

1) Connect to postgres user:

command:

su - postgres

2) Find out the postgres process running:

command:

ps -ef|grep -i postgres

3) echo $PATH and $PGDATA variable

command:

echo $PATH

echo $PGDATA

4) Find out the location of psql

command:

which psql

5) Collect the cron job details

command:

crontab -l

6) Collect df -h output

command:

df -h

7) Connect to psql

command:

psql

8) Collect the db list

command:

\l

or 

select a.oid

,a.datname

,b.rolname owner

,a.encoding

,a.dattablespace

,a.datcollate

,a.datctype

,a.datacl

from pg_database a

,pg_authid b

where b.oid=a.datdba;

9) Connect to the db where the table is located

command:

\c <dbname>

10) View the schema list

command:

select * from pg_catalog.pg_namespace;

or

\dn+

11) show your search path first

command:

show search_path;

12) Set the search path the candidate schema

command:

SET search_path TO <candidate_schema>;

13) show your search path again [remember not to disconnect or exit this connection]

command:

show search_path;

14) List the tables along with their sizes

command:

\d+

15) Collect more details on the table

command:

select schemaname,tablename,tableowner,hasindexes from pg_tables where schemaname='<candidate_schema>' order by 1;

select relname table_name

,relnamespace schema_name

,reltype relation_type

,relowner owner

,relpages pages_or_blocks

,reltuples numrows

,relhasindex has_index

from pg_class

where relnamespace=(select oid from pg_namespace where nspname=<candidate_schema>')

and relname='<candidate_table>' order by 2,1;

16) Take description details

command:

\d <candidate table_name>;

17) Let us examine the index status

command:

select c.schemaname

,c.tablename

,c.indexname

,b.indisunique

,b.indisprimary

,b.indisclustered

,b.indisvalid

,b.indisready

,b.indislive

from pg_class a

,pg_index b

,pg_indexes c

,pg_namespace d

where d.nspname='<candidate_schema>'

and c.schemaname=d.nspname

and c.tablename='<candidate_table>'

and c.indexname=a.relname

and a.oid=b.indexrelid

and a.relnamespace=d.oid

;

18) Now secure backup of the candidate table [in case needed, we can revert both table structure and data]

command:

pg_dump -U <candidate_username> -d <candidate_database> -t <candidate_schema>.<candidate_table> > <candidate_mountspace>/pgtst_schema_pgtst_tbl2.sql

19) Let us run cluster command: << outage begins here

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

cluster verbose <candidate_schemaname>.<candidate_tablename> using <candidate_index>;

20) Let us run analyze

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

analyze verbose <candidate_schemaname>.<candidate_tablename>;

20) Let us examine the index status again

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

select c.schemaname

,c.tablename

,c.indexname

,b.indisunique

,b.indisprimary

,b.indisclustered

,b.indisvalid

,b.indisready

,b.indislive

from pg_class a

,pg_index b

,pg_indexes c

,pg_namespace d

where d.nspname='<candidate_schema>'

and c.schemaname=d.nspname

and c.tablename='<candidate_table>'

and c.indexname=a.relname

and a.oid=b.indexrelid

and a.relnamespace=d.oid

;

21) Let us examine the table status again

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

select schemaname,tablename,tableowner,hasindexes from pg_tables where schemaname='<candidate_schema>' order by 1;

select relname table_name

,relnamespace schema_name

,reltype relation_type

,relowner owner

,relpages pages_or_blocks

,reltuples numrows

,relhasindex has_index

from pg_class

where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema')

and relname='<candidate_table>' order by 2,1;

\dn+

This closes the command summary for clustering operation.

postgresql physical backup - BARMAN

Objective: In this blog, we will examine the steps involved in barman backup configuration.

BARMAN has to be installed in a seperate server than the postgresql server.
Since my machine is very small in size, I installed barman on the same machine as postgresql server.
As per instruction from https://www.digitalocean.com/community/tutorials/how-to-back-up-restore-and-migrate-postgresql-databases-with-barman-on-centos-7
We already have postgresql server installed (version 15). Also we have epel and postgresql yum repos needed for this install step (see the packages downloaded were from either base, epel or pgdg-common).
We just need to install barman using yum install...

Actual output:

[root@10 ~]# yum install barman
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
epel/x86_64/metalink                                                                               | 6.1 kB  00:00:00
 * base: centos.excellmedia.net
 * epel: mirrors.bestthaihost.com
 * extras: centos.excellmedia.net
 * updates: centos.excellmedia.net
base                                                                                               | 3.6 kB  00:00:00
epel                                                                                               | 4.7 kB  00:00:00
extras                                                                                             | 2.9 kB  00:00:00
pgdg-common/7/x86_64/signature                                                                     |  198 B  00:00:00
pgdg-common/7/x86_64/signature                                                                     | 2.9 kB  00:00:00 !!!
pgdg10/7/x86_64/signature                                                                          |  198 B  00:00:00
pgdg10/7/x86_64/signature                                                                          | 3.6 kB  00:00:00 !!!
pgdg11/7/x86_64/signature                                                                          |  198 B  00:00:00
pgdg11/7/x86_64/signature                                                                          | 3.6 kB  00:00:00 !!!
pgdg12/7/x86_64/signature                                                                          |  198 B  00:00:00
pgdg12/7/x86_64/signature                                                                          | 3.6 kB  00:00:00 !!!
pgdg13/7/x86_64/signature                                                                          |  198 B  00:00:00
pgdg13/7/x86_64/signature                                                                          | 3.6 kB  00:00:00 !!!
pgdg14/7/x86_64/signature                                                                          |  198 B  00:00:00
pgdg14/7/x86_64/signature                                                                          | 3.6 kB  00:00:00 !!!
pgdg15/7/x86_64/signature                                                                          |  198 B  00:00:00
pgdg15/7/x86_64/signature                                                                          | 3.6 kB  00:00:00 !!!
updates                                                                                            | 2.9 kB  00:00:00
(1/8): pgdg10/7/x86_64/primary_db                                                                  | 396 kB  00:00:12
(2/8): pgdg11/7/x86_64/primary_db                                                                  | 440 kB  00:00:13
(3/8): pgdg13/7/x86_64/primary_db                                                                  | 224 kB  00:00:02
(4/8): pgdg14/7/x86_64/primary_db                                                                  | 146 kB  00:00:01
(5/8): pgdg15/7/x86_64/primary_db                                                                  |  56 kB  00:00:00
(6/8): pgdg12/7/x86_64/primary_db                                                                  | 326 kB  00:00:17
(7/8): epel/x86_64/primary_db                                                                      | 7.0 MB  00:00:29
(8/8): epel/x86_64/updateinfo                                                                      | 1.0 MB  00:04:03
Resolving Dependencies
--> Running transaction check
---> Package barman.noarch 0:3.2.0-1.rhel7 will be installed
--> Processing Dependency: python-barman = 3.2.0 for package: barman-3.2.0-1.rhel7.noarch
--> Processing Dependency: rsync >= 3.0.4 for package: barman-3.2.0-1.rhel7.noarch
--> Running transaction check
---> Package python-barman.noarch 0:3.2.0-1.rhel7 will be installed
--> Processing Dependency: python-psycopg2 >= 2.4.2 for package: python-barman-3.2.0-1.rhel7.noarch
--> Processing Dependency: python-setuptools for package: python-barman-3.2.0-1.rhel7.noarch
--> Processing Dependency: python-dateutil for package: python-barman-3.2.0-1.rhel7.noarch
--> Processing Dependency: python-argcomplete for package: python-barman-3.2.0-1.rhel7.noarch
---> Package rsync.x86_64 0:3.1.2-11.el7_9 will be installed
--> Running transaction check
---> Package python-dateutil.noarch 0:1.5-7.el7 will be installed
---> Package python-setuptools.noarch 0:0.9.8-7.el7 will be installed
--> Processing Dependency: python-backports-ssl_match_hostname for package: python-setuptools-0.9.8-7.el7.noarch
---> Package python2-argcomplete.noarch 0:1.7.0-4.el7 will be installed
---> Package python2-psycopg2.x86_64 0:2.8.6-1.rhel7 will be installed
--> Running transaction check
---> Package python-backports-ssl_match_hostname.noarch 0:3.5.0.1-1.el7 will be installed
--> Processing Dependency: python-ipaddress for package: python-backports-ssl_match_hostname-3.5.0.1-1.el7.noarch
--> Processing Dependency: python-backports for package: python-backports-ssl_match_hostname-3.5.0.1-1.el7.noarch
--> Running transaction check
---> Package python-backports.x86_64 0:1.0-8.el7 will be installed
---> Package python-ipaddress.noarch 0:1.0.16-2.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==========================================================================================================================
 Package                                        Arch              Version                    Repository              Size
==========================================================================================================================
Installing:
 barman                                         noarch            3.2.0-1.rhel7              pgdg-common             44 k
Installing for dependencies:
 python-backports                               x86_64            1.0-8.el7                  base                   5.8 k
 python-backports-ssl_match_hostname            noarch            3.5.0.1-1.el7              base                    13 k
 python-barman                                  noarch            3.2.0-1.rhel7              pgdg-common            451 k
 python-dateutil                                noarch            1.5-7.el7                  base                    85 k
 python-ipaddress                               noarch            1.0.16-2.el7               base                    34 k
 python-setuptools                              noarch            0.9.8-7.el7                base                   397 k
 python2-argcomplete                            noarch            1.7.0-4.el7                epel                    49 k
 python2-psycopg2                               x86_64            2.8.6-1.rhel7              pgdg-common            171 k
 rsync                                          x86_64            3.1.2-11.el7_9             updates                408 k
Transaction Summary
==========================================================================================================================
Install  1 Package (+9 Dependent packages)
Total download size: 1.6 M
Installed size: 6.5 M
Is this ok [y/d/N]: y
Downloading packages:
(1/10): python-backports-1.0-8.el7.x86_64.rpm                                                      | 5.8 kB  00:00:06
(2/10): python-backports-ssl_match_hostname-3.5.0.1-1.el7.noarch.rpm                               |  13 kB  00:00:06
(3/10): python-dateutil-1.5-7.el7.noarch.rpm                                                       |  85 kB  00:00:07
(4/10): barman-3.2.0-1.rhel7.noarch.rpm                                                            |  44 kB  00:00:08
(5/10): python2-psycopg2-2.8.6-1.rhel7.x86_64.rpm                                                  | 171 kB  00:00:04
(6/10): python-ipaddress-1.0.16-2.el7.noarch.rpm                                                   |  34 kB  00:00:06
(7/10): python-setuptools-0.9.8-7.el7.noarch.rpm                                                   | 397 kB  00:00:08
(8/10): python-barman-3.2.0-1.rhel7.noarch.rpm                                                     | 451 kB  00:00:16
(9/10): python2-argcomplete-1.7.0-4.el7.noarch.rpm                                                 |  49 kB  00:00:08
(10/10): rsync-3.1.2-11.el7_9.x86_64.rpm                                                           | 408 kB  00:00:06
--------------------------------------------------------------------------------------------------------------------------
Total                                                                                      84 kB/s | 1.6 MB  00:00:19
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : python2-psycopg2-2.8.6-1.rhel7.x86_64                                                                 1/10
  Installing : python-ipaddress-1.0.16-2.el7.noarch                                                                  2/10
  Installing : python-backports-1.0-8.el7.x86_64                                                                     3/10
  Installing : python-backports-ssl_match_hostname-3.5.0.1-1.el7.noarch                                              4/10
  Installing : python-setuptools-0.9.8-7.el7.noarch                                                                  5/10
  Installing : python2-argcomplete-1.7.0-4.el7.noarch                                                                6/10
  Installing : python-dateutil-1.5-7.el7.noarch                                                                      7/10
  Installing : python-barman-3.2.0-1.rhel7.noarch                                                                    8/10
  Installing : rsync-3.1.2-11.el7_9.x86_64                                                                           9/10
  Installing : barman-3.2.0-1.rhel7.noarch                                                                          10/10
  Verifying  : rsync-3.1.2-11.el7_9.x86_64                                                                           1/10
  Verifying  : python-backports-ssl_match_hostname-3.5.0.1-1.el7.noarch                                              2/10
  Verifying  : python-dateutil-1.5-7.el7.noarch                                                                      3/10
  Verifying  : python2-argcomplete-1.7.0-4.el7.noarch                                                                4/10
  Verifying  : barman-3.2.0-1.rhel7.noarch                                                                           5/10
  Verifying  : python-backports-1.0-8.el7.x86_64                                                                     6/10
  Verifying  : python-ipaddress-1.0.16-2.el7.noarch                                                                  7/10
  Verifying  : python2-psycopg2-2.8.6-1.rhel7.x86_64                                                                 8/10
  Verifying  : python-barman-3.2.0-1.rhel7.noarch                                                                    9/10
  Verifying  : python-setuptools-0.9.8-7.el7.noarch                                                                 10/10
Installed:
  barman.noarch 0:3.2.0-1.rhel7
Dependency Installed:
  python-backports.x86_64 0:1.0-8.el7                python-backports-ssl_match_hostname.noarch 0:3.5.0.1-1.el7
  python-barman.noarch 0:3.2.0-1.rhel7               python-dateutil.noarch 0:1.5-7.el7
  python-ipaddress.noarch 0:1.0.16-2.el7             python-setuptools.noarch 0:0.9.8-7.el7
  python2-argcomplete.noarch 0:1.7.0-4.el7           python2-psycopg2.x86_64 0:2.8.6-1.rhel7
  rsync.x86_64 0:3.1.2-11.el7_9
Complete!

[root@10 ~]# id barman
uid=998(barman) gid=996(barman) groups=996(barman)
[root@10 ~]#

We alrady have the pg_hba.conf with the below entry, this means that connections coming from 127.0.0.1/32 with all users by default will be allowed without password prompt.

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

Let us setup passwordless ssh setup between barman and postgres user...
The passwordless was setup using the below command

ssh-keygen -t rsa

The command has to be run as both postgres and barman user. The .pub key of both the users needs to be put into authorized_keys file of the .ssh directories of the corresponding user.

Still I faced issues with postgres user (postgres user's password was always prompted), mistake I made was I copied authorized_keys file from barman, it messed up my selinux context for ever... I try remove and readd of the authorized_keys file in the user home. The issue was still the same.

Later I ran the below set of command as postgres user

chmod 755 /var/lib/pgsql/.ssh
chmod 600 /var/lib/pgsql/.ssh/authorized_keys
restorecon -R -v /var/lib/pgsql/.ssh

Now the passwordless ssh worked fine.
Now we need to update /etc/barman.conf, which is at the moment owned by root.
So if in case we have sudo setup for barman we can use it or login as root to perform the below operation....

[barman]
compression = gzip
immediate_checkpoint = false
basebackup_retry_times = 3
basebackup_retry_sleep = 30
last_backup_maximum_age = 1 DAYS
[per postgres instance]
description = "Main Postgres1l 15 DB Server"
ssh_command = ssh postgres@127.0.0.1
conninfo = host=127.0.0.1 user=postgres
retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days
wal_retention_policy = main

>>>> configuration @ barman side is complete. Final config values are...

[root@10 ~]# grep -Ev ";|^$" /etc/barman.conf
[barman]
barman_user = barman
configuration_files_directory = /etc/barman.d
barman_home = /var/lib/barman
log_file = /var/log/barman/barman.log
log_level = INFO
compression = gzip
immediate_checkpoint = false
basebackup_retry_times = 3
basebackup_retry_sleep = 30
last_backup_maximum_age = 1 DAYS
[postgres15-dbserver]
description = "Main Postgres1l 15 DB Server"
ssh_command = ssh postgres@127.0.0.1
conninfo = host=127.0.0.1 user=postgres
retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days
wal_retention_policy = main
[root@10 ~]#

Find out the incoming_wals_directory:
barman show-server postgres15-dbserver | grep incoming_wals_directory

Actual output:

-bash-4.2$ barman show-server postgres15-dbserver | grep incoming_wals_directory
WARNING: No backup strategy set for server 'postgres15-dbserver' (using default 'concurrent_backup').
WARNING: No archiver enabled for server 'postgres15-dbserver'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
        incoming_wals_directory: /var/lib/barman/postgres15-dbserver/incoming
-bash-4.2$

Now changed the postgresql.conf with the below setting & restarted postgresql service:

-bash-4.2$ grep -E "wal_level|archive_mode|archive_command" postgresql.conf
wal_level = replica                     # minimal, replica, or logical
archive_mode = on               # enables archiving; off, on, or always
                                # (empty string indicates archive_command should
archive_command = 'rsync -a %p barman@127.0.0.1:/var/lib/barman/postgres15-dbserver/incoming/%f'                # command to use to archive a logfile segment
-bash-4.2$

[root@10 ~]# systemctl restart postgresql-15
[root@10 ~]# systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/postgresql-15.service.d
           └─override.conf
   Active: active (running) since Tue 2022-10-25 22:06:25 IST; 6s ago
     Docs: https://www.postgresql.org/docs/15/static/
  Process: 1985 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 1990 (postmaster)
   CGroup: /system.slice/postgresql-15.service
           ├─1990 /usr/pgsql-15/bin/postmaster -D /pgDATA/data
           ├─1992 postgres: logger
           ├─1993 postgres: checkpointer
           ├─1994 postgres: background writer
           ├─1996 postgres: walwriter
           ├─1997 postgres: autovacuum launcher
           ├─1998 postgres: archiver
           └─1999 postgres: logical replication launcher
Oct 25 22:06:24 10.0.2.4 systemd[1]: Stopped PostgreSQL 15 database server.
Oct 25 22:06:24 10.0.2.4 systemd[1]: Starting PostgreSQL 15 database server...
Oct 25 22:06:25 10.0.2.4 postmaster[1990]: 2022-10-25 22:06:25.210 IST [1990] LOG:  redirecting log output to logg...ocess
Oct 25 22:06:25 10.0.2.4 postmaster[1990]: 2022-10-25 22:06:25.210 IST [1990] HINT:  Future log output will appear...log".
Oct 25 22:06:25 10.0.2.4 systemd[1]: Started PostgreSQL 15 database server.
Hint: Some lines were ellipsized, use -l to show in full.
[root@10 ~]#

BARMAN check:

command: barman check postgres15-dbserver
-bash-4.2$ barman check postgres15-dbserver
WARNING: No backup strategy set for server 'postgres15-dbserver' (using default 'concurrent_backup').
WARNING: No archiver enabled for server 'postgres15-dbserver'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
Server postgres15-dbserver:
        WAL archive: FAILED (please make sure WAL shipping is setup) <<<<<<<<<< To work around this error, we have to run the next command.
        PostgreSQL: OK
        superuser or standard user with backup privileges: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
        backup minimum size: OK (0 B)
        wal maximum age: OK (no last_wal_maximum_age provided)
        wal size: OK (0 B)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 0 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        systemid coherence: OK (no system Id stored on disk)
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK
-bash-4.2$

I have kept irewalld down for now.

barman switch-wal --force --archive postgres15-dbserver

Actual output:

-bash-4.2$  barman switch-wal --force --archive postgres15-dbserver
WARNING: No backup strategy set for server 'postgres15-dbserver' (using default 'concurrent_backup').
WARNING: No archiver enabled for server 'postgres15-dbserver'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
The WAL file 000000010000000100000000 has been closed on server 'postgres15-dbserver'
Waiting for the WAL file 000000010000000100000000 from server 'postgres15-dbserver' (max: 30 seconds)
Processing xlog segments from file archival for postgres15-dbserver
        000000010000000100000000

>>>> the switch-wal finished fine.

-bash-4.2$ barman check postgres15-dbserver
WARNING: No backup strategy set for server 'postgres15-dbserver' (using default 'concurrent_backup').
WARNING: No archiver enabled for server 'postgres15-dbserver'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
Server postgres15-dbserver:
        PostgreSQL: OK <<<< No more error or warnings.
        superuser or standard user with backup privileges: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
        backup minimum size: OK (0 B)
        wal maximum age: OK (no last_wal_maximum_age provided)
        wal size: OK (0 B)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 0 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        systemid coherence: OK (no system Id stored on disk)
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK
-bash-4.2$

barman list-server

Actual output:

-bash-4.2$ barman list-server
WARNING: No backup strategy set for server 'postgres15-dbserver' (using default 'concurrent_backup').
WARNING: No archiver enabled for server 'postgres15-dbserver'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
postgres15-dbserver - Main Postgres1l 15 DB Server
-bash-4.2$

Creating the first backup:

barman backup postgres15-dbserver

Actual output:

-bash-4.2$ barman backup postgres15-dbserver
WARNING: No backup strategy set for server 'postgres15-dbserver' (using default 'concurrent_backup').
WARNING: No archiver enabled for server 'postgres15-dbserver'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
Starting backup using rsync-concurrent method for server postgres15-dbserver in /var/lib/barman/postgres15-dbserver/base/20221025T233915
Backup start at LSN: 1/2000028 (000000010000000100000002, 00000028)
This is the first backup for server postgres15-dbserver
WAL segments preceding the current backup have been found:
        0000000100000000000000FD from server postgres15-dbserver has been removed
        0000000100000000000000FE from server postgres15-dbserver has been removed
        0000000100000000000000FF from server postgres15-dbserver has been removed
        000000010000000100000000 from server postgres15-dbserver has been removed
Starting backup copy via rsync/SSH for 20221025T233915
Copy done (time: 1 minute, 52 seconds)
This is the first backup for server postgres15-dbserver
WAL segments preceding the current backup have been found:
        000000010000000100000001 from server postgres15-dbserver has been removed
Asking PostgreSQL server to finalize the backup.
Backup size: 2.1 GiB
Backup end at LSN: 1/2000170 (000000010000000100000002, 00000170)
Backup completed (start time: 2022-10-25 23:39:15.563684, elapsed time: 2 minutes, 1 second)
Processing xlog segments from file archival for postgres15-dbserver
        000000010000000100000002
        000000010000000100000002.00000028.backup
        000000010000000100000003
-bash-4.2$

barman list-backup postgres15-dbserver

Actual output:

-bash-4.2$ barman list-backup postgres15-dbserver
WARNING: No backup strategy set for server 'postgres15-dbserver' (using default 'concurrent_backup').
WARNING: No archiver enabled for server 'postgres15-dbserver'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
postgres15-dbserver 20221025T233915 - Tue Oct 25 23:41:11 2022 - Size: 2.1 GiB - WAL Size: 16.0 KiB
-bash-4.2$
barman list-files postgres15-dbserver 20221025T233915
Actual output:
-bash-4.2$ barman list-files postgres15-dbserver 20221025T233915
WARNING: No backup strategy set for server 'postgres15-dbserver' (using default 'concurrent_backup').
WARNING: No archiver enabled for server 'postgres15-dbserver'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
/var/lib/barman/postgres15-dbserver/base/20221025T233915/backup.info
/var/lib/barman/postgres15-dbserver/base/20221025T233915/data/PG_VERSION
/var/lib/barman/postgres15-dbserver/base/20221025T233915/data/backup_label
/var/lib/barman/postgres15-dbserver/base/20221025T233915/data/current_logfiles
...
/var/lib/barman/postgres15-dbserver/base/20221025T233915/data/global/6247
/var/lib/barman/postgres15-dbserver/base/20221025T233915/data/global/pg_control
/var/lib/barman/postgres15-dbserver/base/20221025T233915/data/global/pg_filenode.map
/var/lib/barman/postgres15-dbserver/base/20221025T233915/data/pg_logical/replorigin_checkpoint
/var/lib/barman/postgres15-dbserver/base/20221025T233915/data/pg_multixact/members/0000
/var/lib/barman/postgres15-dbserver/base/20221025T233915/data/pg_multixact/offsets/0000
/var/lib/barman/postgres15-dbserver/base/20221025T233915/data/pg_xact/0000
/var/lib/barman/postgres15-dbserver/wals/0000000100000001/000000010000000100000002
-bash-4.2$
After last backup no more FAILED state in check output of barman.
-bash-4.2$ barman check postgres15-dbserver
WARNING: No backup strategy set for server 'postgres15-dbserver' (using default 'concurrent_backup').
WARNING: No archiver enabled for server 'postgres15-dbserver'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
Server postgres15-dbserver:
        PostgreSQL: OK
        superuser or standard user with backup privileges: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (interval provided: 1 day, latest backup age: 8 minutes, 34 seconds)
        backup minimum size: OK (2.1 GiB)
        wal maximum age: OK (no last_wal_maximum_age provided)
        wal size: OK (16.0 KiB)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 1 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        systemid coherence: OK
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK
-bash-4.2$

This completes barman backup. We have to schedule regular backup in cron of barman.

Thanks

postgresql: pg_dump scenario 1 - simple table backup using pg_dump to script file and restore it using psql

This is a part 1 of the multi part blog series for pg_dump usage.

Objective: pg_dump scenario 1 - simple table backup using pg_dump to script file and restore it using psql

pg_dump notes:

Allows backup of the entire database in script or archive format.

Script format is plain text format.
Archive format can be restored only using pg_restore, this allows performing data selection & reordering.
Flexible Output file formats for archive are: Fc - custom format, Fd - directory format
Archive format supports - compession, parallel restore,select/reordering
User super user to perform backup/restore
If in case you want to restore with different owner, you have to use -O option in pg_dump
-t is used to mention the table to be backed up
-T exclude tables mentioned
-n include schemas
-N exclude schemas
-Z 0..9 [compression level] supported only for plain script and directory output,not for tar format
pg_dump (lower) -> server (higher) = OK
pg_dump (higher) -> server (lower) = NOK
Using pg_dump to backup the table pgtst_tbl2 and restore it in the same db and new db...

Example - plain script:
pg_dump -U pgtst_usr -d pgtst_db -t pgtst_schema.pgtst_tbl2 > /pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql

Actual output:

-bash-4.2$ pwd

/pgDATA/pgdump/plainscript

-bash-4.2$ pg_dump -U pgtst_usr -d pgtst_db -t pgtst_schema.pgtst_tbl2 > /pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql

Password:

>>> no response here, looks like a successful finish

-bash-4.2$ ls -altr

total 60

drwxr-xr-x. 3 postgres postgres    25 Oct 25 13:38 ..

drwxr-xr-x. 2 postgres postgres    41 Oct 25 13:39 .

-rw-r--r--. 1 postgres postgres 58691 Oct 25 13:39 pgtst_schema_pgtst_tbl2.sql

-bash-4.2$ view pgtst_schema_pgtst_tbl2.sql

-bash-4.2$

Let us now restore this table to a new db:

create database pgtst_db2;

postgres=> \l
                                                  List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+-----------+----------+-------------+-------------+------------+-----------------+-----------------------
 pgtst_db  | pgtst_usr | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |           |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |           |          |             |             |            |                 | postgres=CTc/postgres
(4 rows)
postgres=> \l+
                                                                                    List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   |  Si
ze   | Tablespace |                Description
-----------+-----------+----------+-------------+-------------+------------+-----------------+-----------------------+----
-----+------------+--------------------------------------------
 pgtst_db  | pgtst_usr | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |                       | 208
9 MB | pg_default |
 postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |                       | 759
7 kB | pg_default | default administrative connection database
 template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +| 744
1 kB | pg_default | unmodifiable empty database
           |           |          |             |             |            |                 | postgres=CTc/postgres |
     |            |
 template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +| 751
3 kB | pg_default | default template for new databases
           |           |          |             |             |            |                 | postgres=CTc/postgres |
     |            |
(4 rows)

postgres=> create database pgtst_db2;
CREATE DATABASE

postgres=> \l
                                                  List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+-----------+----------+-------------+-------------+------------+-----------------+-----------------------
 pgtst_db  | pgtst_usr | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 pgtst_db2 | pgtst_usr | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |           |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |           |          |             |             |            |                 | postgres=CTc/postgres
(5 rows)
postgres=>

psql -U pgtst_usr -d pgtst_db2 -f /pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql

this failed - reason, the schema isnt created, we just created the db.

Actual output:

-bash-4.2$ psql -U pgtst_usr -d pgtst_db2 -f /pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql

Password for user pgtst_usr:

SET
SET
SET
SET
SET
 set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:32: ERROR:  schema "pgtst_schema" does not exist
LINE 1: CREATE TABLE pgtst_schema.pgtst_tbl2 (
                     ^
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:35: ERROR:  schema "pgtst_schema" does not exist
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:41: ERROR:  schema "pgtst_schema" does not exist
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:1041: error: invalid command \.
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:1049: ERROR:  syntax error at or near "1"
LINE 1: 1 T 1
        ^
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:1051: ERROR:  schema "pgtst_schema" does not exist
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:1058: ERROR:  schema "pgtst_schema" does not exist

Now let us create the schema and retry the operation:

pgtst_db2=> \conninfo
You are connected to database "pgtst_db2" as user "pgtst_usr" via socket in "/var/run/postgresql" at port "5432".

pgtst_db2=> \d
Did not find any relations.

pgtst_db2=> select count(1) from pg_class;
 count
-------
   410
(1 row)

pgtst_db2=> select count(1) from pg_tables;
 count
-------
    68
(1 row)

pgtst_db2=> select distinct(schemaname) from pg_tables;
     schemaname
--------------------
 information_schema
 pg_catalog
(2 rows)
pgtst_db2=>

pgtst_db2=> create schema pgtst_schema; <
CREATE SCHEMA

pgtst_db2=>

Reattempt to load or restore the data:

-bash-4.2$ psql -U pgtst_usr -d pgtst_db2 -f /pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql
Password for user pgtst_usr:
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 999
ALTER TABLE
ALTER TABLE
CREATE INDEX
-bash-4.2$

pgtst_db2=> select relname,reltype,relpages,reltuples,relhasindex from pg_class where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema');
      relname       | reltype | relpages | reltuples | relhasindex
--------------------+---------+----------+-----------+-------------
 pgtst_tbl2         |   24632 |       14 |       999 | t
 pgtst_tbl2_pkey    |       0 |        5 |       999 | f
 pgtst_tbl2_idx_age |       0 |        4 |       999 | f
(3 rows)

pgtst_db2=> analyze pgtst_schema.pgtst_tbl2;
ANALYZE

pgtst_db2=>

pgtst_db2=> select * from pg_stat_all_tables where schemaname='pgtst_schema';
 relid |  schemaname  |  relname   | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_de
l | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum | last_vacuum | last_autovacuum |
         last_analyze           |         last_autoanalyze         | vacuum_count | autovacuum_count | analyze_count | aut
oanalyze_count
-------+--------------+------------+----------+--------------+----------+---------------+-----------+-----------+---------
--+---------------+------------+------------+---------------------+--------------------+-------------+-----------------+--
--------------------------------+----------------------------------+--------------+------------------+---------------+----
---------------
 24630 | pgtst_schema | pgtst_tbl2 |        2 |         1998 |        0 |             0 |       999 |         0 |
0 |             0 |        999 |          0 |                   0 |                999 |             |                 | 2
022-10-25 13:53:11.996277+05:30 | 2022-10-25 13:50:57.759322+05:30 |            0 |                0 |             1 |
             1
(1 row)
pgtst_db2=>

Look there the autoanalyze is completed around the same time the data restore finished using psql.

Analyze is complete as well.

Thanks