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.