How To Basic Basebackup in PostgreSQL

Hello,

I would like to share my knowledge about how to get basebackup in PostgreSQL and with getting lots of errors and how to overcome it. So you should be ready to getting crash and doing stupid things and to be achieve!

First of all, you need to make sure if your WAL archiving is enabled. You can look at Postgres.conf under cluster data file.

Following steps you can connect your database as super user to continue but I'll use psql in bash.

Access your environment and connect as postgres user and continue as bellow;


[root@gunce]#sudo su - postgres
-bash-4.2$ psql -d backup_test -c "select pg_start_backup('backuptest');"
 pg_start_backup
-----------------
 0/F000028
(1 row)

-bash-4.2$ tar -czf gunce.tar.gz data/* -C /var/lib/pgsql/9.6/
-bash-4.2$ psql -d backup_test -c "select pg_stop_backup();"
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup
----------------
 0/F000130
(1 row)


Restart your postgresql service. To test and see what is gonna do after restore, you can create a table or insert rows to a exist table. I'll insert rows to a exist table bellow;


psql -d backup_test -c "select * from dummy_table;"
 a
---
(0 rows)

psql -d backup_test -c "insert into dummy_table select * from dummy_table2;"
INSERT 0 3


Now, dummy_table has 3 rows but before backup the table is not any row.

We need to recovery.conf file to restore our backup.

-bash-4.2$ logout
[guncek@gunce ~]$ sudo su -
[sudo] password for guncek:
Last login: Tue Jun 13 07:18:12 +03 2017 on pts/0
[root@gunce ~]# updatedb
[root@gunce ~]# locate recovery.conf
/usr/pgsql-9.6/share/recovery.conf.sample
[root@gunce ~]# cp /usr/pgsql-9.6/share/recovery.conf.sample /var/lib/pgsql/9.6/data/recovery.conf


I copy recovery.conf.sample file under cluster data file and change a row in that file.


restore_command = '/bin/cp /var/lib/pgsql/archive/%f "%p"'


Restore your backup!


[root@gunce data]# sudo su - postgres
-bash-4.2$ ls -la
total 80
drwxr-xr-x 20 postgres postgres  4096 Jun 13 17:28 .
drwx------  5 postgres postgres    83 Jun 13 17:06 ..
....
....
....
-rw-r--r--  1 root     root      5727 Jun 13 17:27 recovery.conf


The recovery.conf file's owner is root and when we restart postgresql service probably we'll get error about this.

And I try,


-bash-4.2$ service postgresql-9.6 start
Redirecting to /bin/systemctl start  postgresql-9.6.service


Wow! still we did not faced with an error! We can connect to postgresql database :)


-bash-4.2$ psql -d backup_test
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?



Ups..! This error totally about our recovery.conf check your log file.


-bash-4.2$ tail -n 5 data/pg_log/postgresql-Tue.log
< 2017-06-13 18:26:47.741 +03 > FATAL:  could not open file "recovery.conf": Permission denied
< 2017-06-13 18:26:47.742 +03 > LOG:  startup process (PID 5596) exited with exit code 1
< 2017-06-13 18:26:47.742 +03 > LOG:  terminating any other active server processes
< 2017-06-13 18:26:47.745 +03 > LOG:  database system is shut down
-bash-4.2$ cd 9.6/data/
-bash-4.2$ ls -la
total 72
drwx------ 20 postgres postgres  4096 Jun 13 18:26 .
drwx------  5 postgres postgres    83 Jun 13 17:06 ..

....
....
....

-rw-r--r--  1 root     root      5727 Jun 13 17:27 recovery.conf


Need to be superuser to change file's owner.


-bash-4.2$ logout
[root@gunce data]# chown postgres:postgres /var/lib/pgsql/9.6/data/recovery.conf
[root@gunce data]# sudo su - postgres


So, restart your postgresql service again,


-bash-4.2$ systemctl status postgresql-9.6.service
● postgresql-9.6.service - PostgreSQL 9.6 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Tue 2017-06-13 20:29:34 +03; 10min ago
  Process: 6030 ExecStart=/usr/pgsql-9.6/bin/postmaster -D ${PGDATA} (code=exited, status=1/FAILURE)
  Process: 6025 ExecStartPre=/usr/pgsql-9.6/bin/postgresql96-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 6030 (code=exited, status=1/FAILURE)


Run failed row as bellow;


-bash-4.2$ /usr/pgsql-9.6/bin/postmaster -D ${PGDATA}
< 2017-06-13 20:40:40.567 +03 > FATAL:  data directory "/var/lib/pgsql/9.6/data" has group or world access
< 2017-06-13 20:40:40.567 +03 > DETAIL:  Permissions should be u=rwx (0700).


So, we need to change data directory's permissions. But first be superuser.


-bash-4.2$ logout
[root@gunce data]# cd /var/lib/pgsql/9.6
[root@gunce 9.6]# ls -la
total 21704
drwx------  5 postgres postgres       83 Jun 13 19:47 .
drwx------  5 postgres postgres      116 Jun 13 20:30 ..
drwx------  2 postgres postgres        6 Jun 13 19:20 backups
drwxr-xr-x 20 postgres postgres     4096 Jun 13 20:29 data
drwx------ 20 postgres postgres     4096 Jun 13 19:45 data_old
-rw-r--r--  1 postgres postgres 22209955 Jun 13 19:44 gunce.tar.gz
-rw-------  1 postgres postgres     2487 Jun  8 07:21 initdb.log
[root@gunce-test 9.6]# chown 700 data

Restart postgresql service again,

-bash-4.2$ service postgresql-9.6 restart

Finally, try to connect your database and select dummy_table and what we'll gonna see.

-bash-4.2$ psql -d backup_test

Type "help" for help.

backup_test=# select * from asd;

 a

---

(0 rows)

backup_test=#

We see nothing cause, this table was empty before archive data files and we inserted rows after backup. 

Loves,