Steps to restore a PostgreSQL database having multiple tablespaces to different location on the same serve

 

Steps to restore a database having multiple tablespaces to different location on the same server in PostgreSQL 

In this blog post will discuss the steps to restore a database having multiple tablespaces to different location on the same server.

/*BACKUP*/
—————————————————————————————————–

1.     Create multiple database associate to multiple tablespaces.

2.     Check the data dictionary to see the tablespace locations.

3.     Take database backup using pg_basebackup utility.

4.     Check the contents of backup.

Step 1: Create multiple database associate to its individual tablespace :

Create one table and insert data into the table :

Create one more database and tablespace:

Load data into newly created database :

Step 2: Check the data dictionary to see the tablespace locations :

 

Step 3: Using pg_basebackup utility backup the database.

 

Note: The 6 tablespaces which were backed up where one default and rest four newly created.

REPORT THIS AD

Step 4: Check the content of backup

The 16385.tar.gz, 16442.tar.gz, 16426.tar.gz and 16459.tar.gz are created as a backup for the four tablespaces. These are created with the same names as the OIDs of their respective tablespaces.

 

 

 

 

/*RESTORE*/
—————————————————————————————————–

Now let’s take a look at the restoration steps.

1.     Extract the base.tar.gz from the backup to a new location on the same server.

2.     Reading important information’s from backup_label and tablespace_map files.

3.     Extract the OID tar to a new location.

1.     Edit the tablespace map file.

4.     Extract the pg_wal tar to new location.

5.     Create recovery.conf file.

6.     Change the port number and archiving command settings.

7.     Start the database and validate the contents.

Step 1: Extract the base.tar.gz from the backup to a new location on the same server.

Creating directories to hold new data

Step 2: Reading important information’s from backup_label and tablespace_map files.

Check the contents of backup_label. This is needed for PITR recovery.

 

As you can see above the tablespace location of the backed-up database. For our restoration we need to edit this file once we restore the tablespace backup before starting the database.

Step 3: Extract the OID tar to the new location.

Note: If you have multiple tablespaces create appropriate directories before extracting.

 

As you can see under individual new tablespace directory the original DB OID folder is created.

Step 3a: Edit the tablespace map to point the appropriate location.

Step 4: Extract the pg_wal tar to the new location.

Step 5: Create recovery.conf file

Note: This step is optional if you want to start the database directly into read-write mode and no needed to apply additional wal files in some case. Preferable to start the DB in standby mode.

Step 6: Change the port number and archive command parameter.

Since this database is restored on the same server and also this won’t be used to point application hence its ok to disable the archiving parameter.

Step 7: Start the database and validate the contents.