Infrastructure at your Service

Daniel Westermann

Can I do it with PostgreSQL? – 1 – Restore points

When discussing with customers about PostgreSQL we often hear that they can do things in one database that they can not do in PostgreSQL. Most of the times this is not true and you actually can do it in PostgreSQL. Maybe not in exactly the same way but this is not surprising as PostgreSQL does implement features not in exactly the same way other vendors do.

To start this series we’ll talk about restore points. Of course you can create restore points in PostgreSQL and then restore up to such a point in case you need to (e.g. after a failed schema or application upgrade or just for testing purposes ). Lets go…

We’ll use the latest version of PostgreSQL which is 9.6.1 currently:

[email protected]:/home/postgres/ [PG961] sqh
psql (9.6.1 dbi services build)
Type "help" for help.

([email protected][local]:5439) [postgres] > select version();
 PostgreSQL 9.6.1 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)

Time: 47.119 ms
([email protected][local]:5439) [postgres] > 

When we want to do point in time recovery we need to setup archiving. Without going into the details (as this is out of scope here) the parameters which need to be adjusted are these (if not already done):

([email protected][local]:5439) [postgres] > alter system set wal_level = 'replica';
Time: 28.056 ms
([email protected][local]:5439) [postgres] > alter system set archive_command='test ! -f /u90/pgdata/PG961/%f && cp %p /u90/pgdata/PG961/%f'; 
Time: 20.925 ms
([email protected][local]:5439) [postgres] > alter system set archive_mode ='on';
Time: 5.307 ms
([email protected][local]:5439) [postgres] > select name,context from pg_settings where name in ('archive_mode','archive_command','wal_level');
      name       |  context   
 archive_command | sighup
 archive_mode    | postmaster
 wal_level       | postmaster
(3 rows)

Time: 1.460 ms

Be sure to restart your instance before you continue. Changing archive_mode and wal_level can not be done online. Once you restarted make sure that your archive_command really succeeds:

([email protected][local]:5439) [postgres] > create database test1;
Time: 1705.539 ms
([email protected][local]:5439) [postgres] > drop database test1;
Time: 107.283 ms
([email protected][local]:5439) [restore] > select pg_switch_xlog();
(1 row)

Time: 214.216 ms
([email protected][local]:5439) [postgres] > \! ls -l /u90/pgdata/PG961/
total 16384
-rw-------. 1 postgres postgres 16777216 Nov 24 17:34 000000020000000000000022

When you can not see an archived wal in the last step you did something wrong. The next bit you need when you want to do point in time recovery with PostgreSQL is a base backup:

[email protected]:/u02/pgdata/PG961/ [PG961] mkdir /u90/pgdata/PG961/basebackups
[email protected]:/u02/pgdata/PG961/ [PG961] pg_basebackup -x -D /u90/pgdata/PG961/basebackups/
[email protected]:/u02/pgdata/PG961/ [PG961] ls /u90/pgdata/PG961/basebackups/
backup_label  pg_commit_ts   pg_log        pg_replslot   pg_stat_tmp  PG_VERSION
base          pg_dynshmem    pg_logical    pg_serial     pg_subtrans  pg_xlog
global        pg_hba.conf    pg_multixact  pg_snapshots  pg_tblspc
pg_clog       pg_ident.conf  pg_notify     pg_stat       pg_twophase  postgresql.conf

Fine. Lets generate some test data with this simple script:

([email protected][local]:5439) [postgres] > \! cat a.sql
\c postgres
drop database if exists restore;
create database restore;
\c restore
create table t1 ( a int );
insert into t1 (a)
       values (generate_series(1,1000000));
select count(*) from t1;
\d t1

When you run this you’ll get a table (t1) containing 1 million rows:

([email protected][local]:5439) [postgres] > \i a.sql
You are now connected to database "postgres" as user "postgres".
Time: 114.000 ms
Time: 1033.245 ms
You are now connected to database "restore" as user "postgres".
Time: 5.917 ms
INSERT 0 1000000
Time: 2226.599 ms
(1 row)

Time: 65.864 ms
      Table "public.t1"
 Column |  Type   | Modifiers 
 a      | integer | 

Ok, fine. Now we are ready for testing restore points. Lets say you want to do some modifications to your table and to be on the safe side you want to create a restore point before. No problem:

([email protected][local]:5439) [postgres] > select pg_create_restore_point('RP1');
(1 row)

Time: 0.825 ms

Quite easy and fast. Now lets play with our table:

([email protected][local]:5439) [restore] > select count(*) from t1;
(1 row)

Time: 66.214 ms
([email protected][local]:5439) [restore] > \d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
 a      | integer | 

([email protected][local]:5439) [restore] > alter table t1 add column b varchar(10);
Time: 1.810 ms
([email protected][local]:5439) [restore] > update t1 set b='b';
UPDATE 1000010
Time: 11004.972 ms
([email protected][local]:5439) [restore] > drop table t1;
Time: 238.329 ms

Ups, table gone. How can we now go back to the restore point created above? Quite easy:

Shutdown your instance and copy back the base backup:

[email protected]:/u02/pgdata/PG961/ [PG961] rm -rf pg_xlog
[email protected]:/u02/pgdata/PG961/ [PG961] cp -pr /u90/pgdata/PG961/basebackups/* $PGDATA
cp: cannot overwrite non-directory ‘/u02/pgdata/PG961/pg_xlog’ with directory ‘/u90/pgdata/PG961/basebackups/pg_xlog’
[email protected]:/u02/pgdata/PG961/ [PG961] ln -s /u03/pgdata/PG961/ pg_xlog

Then create a recovery.conf file (for telling PostgreSQL to go into recovery mode when it comes up) and specify the restore point you created above:

[email protected]:/home/postgres/ [PG961] echo "restore_command = 'cp /u90/pgdata/PG961/%f %p'
> recovery_target_name = 'RP1'" > $PGDATA/recovery.conf
[email protected]:/home/postgres/ [PG961] cat $PGDATA/recovery.conf
restore_command = 'cp /u90/pgdata/PG961/%f %p'
recovery_target_name = 'RP1'

Start the instance and check the log file:

LOG:  database system was interrupted; last known up at 2016-11-24 17:36:28 CET
LOG:  creating missing WAL directory "pg_xlog/archive_status"
LOG:  starting point-in-time recovery to "RP1"

If everything went fine your table should be back without the additional column:

([email protected][local]:5439) [restore] > \d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
 a      | integer | 

([email protected][local]:5439) [restore] > select count(*) from t1;
(1 row)

Time: 82.797 ms

So, yes, you can definitely use restore points with PostgreSQL 🙂

If you want me to blog about any feature you are not sure is there in PostgreSQL let me know.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure