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:

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

(postgres@[local]:5439) [postgres] > select version();
                                                          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
(postgres@[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):

(postgres@[local]:5439) [postgres] > alter system set wal_level = 'replica';
ALTER SYSTEM
Time: 28.056 ms
(postgres@[local]:5439) [postgres] > alter system set archive_command='test ! -f /u90/pgdata/PG961/%f && cp %p /u90/pgdata/PG961/%f'; 
ALTER SYSTEM
Time: 20.925 ms
(postgres@[local]:5439) [postgres] > alter system set archive_mode ='on';
ALTER SYSTEM
Time: 5.307 ms
(postgres@[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:

(postgres@[local]:5439) [postgres] > create database test1;
CREATE DATABASE
Time: 1705.539 ms
(postgres@[local]:5439) [postgres] > drop database test1;
DROP DATABASE
Time: 107.283 ms
(postgres@[local]:5439) [restore] > select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/22001798
(1 row)

Time: 214.216 ms
(postgres@[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:

postgres@pgbox:/u02/pgdata/PG961/ [PG961] mkdir /u90/pgdata/PG961/basebackups
postgres@pgbox:/u02/pgdata/PG961/ [PG961] pg_basebackup -x -D /u90/pgdata/PG961/basebackups/
postgres@pgbox:/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    postgresql.auto.conf
pg_clog       pg_ident.conf  pg_notify     pg_stat       pg_twophase  postgresql.conf

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

(postgres@[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:

(postgres@[local]:5439) [postgres] > i a.sql
You are now connected to database "postgres" as user "postgres".
DROP DATABASE
Time: 114.000 ms
CREATE DATABASE
Time: 1033.245 ms
You are now connected to database "restore" as user "postgres".
CREATE TABLE
Time: 5.917 ms
INSERT 0 1000000
Time: 2226.599 ms
  count  
---------
 1000000
(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:

(postgres@[local]:5439) [postgres] > select pg_create_restore_point('RP1');
 pg_create_restore_point 
-------------------------
 0/28D50EF8
(1 row)

Time: 0.825 ms

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

(postgres@[local]:5439) [restore] > select count(*) from t1;
  count  
---------
 1000010
(1 row)

Time: 66.214 ms
(postgres@[local]:5439) [restore] > d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

(postgres@[local]:5439) [restore] > alter table t1 add column b varchar(10);
ALTER TABLE
Time: 1.810 ms
(postgres@[local]:5439) [restore] > update t1 set b='b';
UPDATE 1000010
Time: 11004.972 ms
(postgres@[local]:5439) [restore] > drop table t1;
DROP TABLE
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:

postgres@pgbox:/u02/pgdata/PG961/ [PG961] rm -rf pg_xlog
postgres@pgbox:/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’
postgres@pgbox:/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:

postgres@pgbox:/home/postgres/ [PG961] echo "restore_command = 'cp /u90/pgdata/PG961/%f %p'
> recovery_target_name = 'RP1'" > $PGDATA/recovery.conf
postgres@pgbox:/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:

(postgres@[local]:5439) [restore] > d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

(postgres@[local]:5439) [restore] > select count(*) from t1;
  count  
---------
 1000000
(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.