How to Backup PostgreSQL Data From Files

This article is about how to make backup of a database from filesystem in PostgreSQL. Also provides details on how to make and restore the database from filesystem. If you have come across case where you are required to start ‘psql’ database from filesystem backup. There might be case that some of Ubuntu files crashed during a blackout. The Ubuntu needs to be reinstalled. You do not have database backup files, you have somehow restored filesystem, or situation like, you forgot the AWS root password and you need to redo all setup right from database on other instance. This post is about second case, where I forgot the AWS root password, so I have attached the filesystem to other instance and trying to get the database up and running from mounted filesystem. There is way to recover the databases from the data folder only. So lets get going on How to Backup PostgreSQL Data From Files.

I am using AWS ec2 instance with focal.

Distributor ID: Ubuntu
Description: Ubuntu 20.04.2 LTS
Release: 20.04
Codename: focal

Lets start on what are steps to recover PostgreSQL databases from raw physical files. To answer this in one line, just save or mount the the data directory to disk and When launching Postgres, set the parameter telling mounted data directory path.

This post assumes your PostgreSQL environment has following setup-

On the new instance or new machine, you have installed postgreSQL
You have created database and added default user, postgres Linux superuser.
- postgres=# create user pguser with encrypted password ‘yourpasswd’;
You have provided admin permission to the default user
- postgres=# grant all privileges on database vtpdb2 to pguser;
Most importantly, your new PostgreSQL version should be same as old version. In this case, I have installed version 10, though latest stable psql version is 13.

Contents

Steps – How to Backup PostgreSQL Data From Files

This section details out steps on how to backup PostgreSQL data from files.

Step 1 – Preparation for backup from raw files

Change ownership of data directory on mounted file system and provide required access. I have mounted data directory in ‘/mnt’ folder.

bhagwat@ip-172-31-14-170:~$ sudo chown -R postgres:postgres /mnt/var/lib/postgresql/10/main/
bhagwat@ip-172-31-14-170:~$ sudo chmod 700 /mnt/var/lib/postgresql/10/main/

Verify this change, since, It is important to take note of the ownership and permissions of the data directory because it is needed that new directory and old directory matches them.

bhagwat@ip-172-31-14-170:~$ sudo ls -la /mnt/var/lib/postgresql/10/main/
total 92
drwx------ 19 postgres postgres 4096 Jul 23 09:30 .
drwxr-xr-x 3 postgres postgres 4096 Jul 23 09:30 ..
-rw------- 1 postgres postgres 3 Jul 23 09:30 PG_VERSION
drwx------ 5 postgres postgres 4096 Jul 23 09:30 base
drwx------ 2 postgres postgres 4096 Jul 23 09:31 global
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_commit_ts
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_dynshmem
drwx------ 4 postgres postgres 4096 Jul 23 09:35 pg_logical
drwx------ 4 postgres postgres 4096 Jul 23 09:30 pg_multixact
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_notify
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_replslot
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_serial
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_snapshots
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_stat
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_subtrans
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_tblspc
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_twophase
drwx------ 3 postgres postgres 4096 Jul 23 09:30 pg_wal
drwx------ 2 postgres postgres 4096 Jul 23 09:30 pg_xact
-rw------- 1 postgres postgres 88 Jul 23 09:30 postgresql.auto.conf
-rw------- 1 postgres postgres 130 Jul 23 09:30 postmaster.opts
-rw------- 1 postgres postgres 109 Jul 23 09:30 postmaster.pid

Stop PostgreSQL and verify its status

bhagwat@ip-172-31-14-170:~$ sudo systemctl stop postgresql
bhagwat@ip-172-31-14-170:~$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Fri 2021-07-23 09:57:13 UTC; 12s ago
Main PID: 93086 (code=exited, status=0/SUCCESS)
Jul 23 03:49:07 ip-172-31-14-170 systemd[1]: Starting PostgreSQL RDBMS…
Jul 23 03:49:07 ip-172-31-14-170 systemd[1]: Finished PostgreSQL RDBMS.
Jul 23 09:57:13 ip-172-31-14-170 systemd[1]: postgresql.service: Succeeded.
Jul 23 09:57:13 ip-172-31-14-170 systemd[1]: Stopped PostgreSQL RDBMS.

Step 2- Changing Default Data Directory

Connect to your default psql database and verify the data directory.

This also shows the default database tables available after postgres installation.

Now change the data directory to mounted data directory. I have mounted data in ‘/mnt’ folder. With older postgresql versions you can also start with data_directory using “-D” option.

$ sudo vim /etc/postgresql/10/main/postgresql.conf

update data_directory in postgresql.conf

Step 3 – Start With New Data Directory

$ sudo service postgresql start
$ sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2021-07-23 10:00:56 UTC; 4s ago
Process: 98844 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 98844 (code=exited, status=0/SUCCESS)
Jul 23 10:00:56 ip-172-31-14-170 systemd[1]: Starting PostgreSQL RDBMS…
Jul 23 10:00:56 ip-172-31-14-170 systemd[1]: Finished PostgreSQL RDBMS.

$ sudo -u postgres psql
psql (10.17 (Ubuntu 10.17-1.pgdg20.04+1))
Type "help" for help.
postgres=# \l
List of databases
Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
pgdb      | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres         +
|          |          |         |         | postgres=CTc/postgres+
|          |          |         |         | pguser=CTc/postgres
postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
|          |          |         |         | postgres=CTc/postgres
template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
|          |          |         |         | postgres=CTc/postgres
vtpdb     | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres         +
|          |          |         |         | postgres=CTc/postgres+
|          |          |         |         | pguser=CTc/postgres
vtpdb1    | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres         +
|          |          |         |         | postgres=CTc/postgres+
|          |          |         |         | pguser=CTc/postgres
vtpdb2    | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres         +
|          |          |         |         | postgres=CTc/postgres+
|          |          |         |         | pguser=CTc/postgres
(7 rows)
postgres=# \c vtpdb2
You are now connected to database "vtpdb2" as user "postgres".

This shows 4 more tables from mounted files system. We are interested in ‘vtpdb2’ database.

Step 4- Backup and Restore

Once the database and its related tables are available you can easily do backup of the database and restore to our default database.

Run pg_dump to back the database. Since you are logged in as different user, you need to mention user specifically, else you will have to do this with root access.

$pg_dump -h localhost -U pguser vtpdb2 > /home/bhagwat/vtpdb2_bkp.sql

Now run pg_restore. Do not forget to provide ‘localhost’ option.

$pg_restore -h localhost -U pguser -d vtpdb2 -1 /home/bhagwat/vtpdb2_bkp.sql

Once this is done, you can revert back the changes in step 2, ie revert to your default data directory.

Troubleshooting –

I have faced few errors as follows

When I run psql -U postgres I get the following error:

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"?

For this I have tried to do the changes in pg_hba.conf. Also tried to do symlink with ‘tmp’ file, but it did not worked out.

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

So, I had to purge the PostgreSQL and install it again to make it work.

Errors while doing pg_restore

$pg_restore -h localhost -U pguser -d vtpdb2 -1 /home/bhagwat/vtpdb2_bkp.sql 
pg_restore: [archiver (db)] Error while processing TOC:
pg_restore: [archiver (db)] Error from TOC entry 196, Table storage_elements pguser
pg_restore: [archiver (db)] Could not execute query: Error: role "pguser" does not exist
    Command was: ALTER Table storage_elements OWNER To pguser

For this error, I have created ‘pguser’ in the default postgresql installation.

Next error was related to access permissions for ‘pguser’

$pg_restore -h localhost -U pguser -d vtpdb2 -1 /home/bhagwat/vtpdb2_bkp.sql 
pg_restore: [archiver (db)] Error while processing TOC: 
pg_restore: [archiver (db)] Error from TOC entry 5843, 0 0 Comment extension plpgsql 
pg_restore: [archiver (db)] Could not execute query: must be owner of extension plpgsql 
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/PgSQL procedural language';

For this error to solve make ‘pguser’ as superuser in PostgreSQL.

I hope you have enjoyed this post on How to Backup PostgreSQL Data From Files.