[BUGS] Enable WAL Archive in Replication server
Hi, I want to enable WAL archive on the replica, I am advised that WAL archive can only be enabled on the Master, can you please advise if this is a bug and any workaround available? regards now you can only enable WAL archive on the Master server,it will not take effect on replication server. I can't find the doc which explicit prohibit WAL archive on the replication server,and there are WAL generated in the slave pg_xlog dir with archive_status subdir,so I think this may be a bug. Jov blog: http:amutu.com/blog 2013/8/11 Hi, I want to archive WAL log files in the replication server as well, the postgresql.conf is added with following new lines, after restarting PG, the /var/pgsql/data/archive/ is still empty after 1 hour: ### new lines added today # # to enable the replica as Hot Standby hot_standby = on # # to enable WAL archive (coped from master's conf) wal_level = hot_standby max_wal_senders = 5 wal_keep_segments = 500 wal_sync_method = fsync fsync = on archive_mode = on archive_command = 'test ! -f /var/pgsql/data/archive/%f && cp %p /var/pgsql/data/archive/%f' ### ls -la /var/pgsql/data/archive/ total 8 drwx-- 2 postgres root 4096 Aug 11 16:12 ./ drwx-- 3 postgres root 4096 Aug 10 10:59 ../ PG: 9.2.4 OS: Ubuntu 12.04 I am new to Streaming Replication, can you please advise why there is no WAL archive files found in the archive folder? regards
[BUGS] Recovery.conf PITR by recovery_target_time
Hi, I have tried following test cases about PITR: a) Time and events on Master : 16:32:03 HKTBegin; CREATE TABLE test22 (id INTEGER PRIMARY KEY); INSERT INTO test22 VALUES (generate_series(1,22)); End; Commit; 16:35:02 HKTBegin; CREATE TABLE test23 (id INTEGER PRIMARY KEY); INSERT INTO test23 VALUES (generate_series(1,23)); End; Commit; 16:57:01 HKTBegin; CREATE TABLE test24 (id INTEGER PRIMARY KEY); INSERT INTO test24 VALUES (generate_series(1,24)); End; Commit; 15:57:45 HKTBegin; CREATE TABLE test25 (id INTEGER PRIMARY KEY); INSERT INTO test25 VALUES (generate_series(1,25)); End; Commit; 16:57:56 HKTdrop table test24; ( 16:58:30 HKT<== recovery_target_time ) 17:01:53 HKTBegin; CREATE TABLE test26 (id INTEGER PRIMARY KEY); INSERT INTO test26 VALUES (generate_series(1,26)); End; Commit; b) Expected Result - Do PITR on another test machine, - Set recovery_target_time = '2013-08-12 16:58:30 HKT', i.e wish to get table test22, test23, test25 restored, but not test24 (dropped) / test26 (after recovery_target_time) - PITR should stop the recovery according to recovery_target_time (i.e. should not replay all WAL) c) Recovery.conf (only 4 lines) restore_command = '/usr/local/pgsql/bin/pg_standby -d -s 2 -t /tmp/pgsql.trigger.5442 /var/pgsql/data/archive/ %f %p %r >> /tmp/pitr.log' recovery_end_command = 'rm -f /tmp/pgsql.trigger.5442' recovery_target_time = '2013-08-12 16:58:30 HKT' recovery_target_inclusive = 'false' d) Result (Not Good ) PG ignored the recovery_target_time, actually it just replayed all WAL which is not good. - Log : LOG: database system was interrupted; last known up at 2013-08-12 17:07:28 HKT LOG: starting point-in-time recovery to 2013-08-12 16:58:30+08 <== showing the point-in-time of the recovery Trigger file: Waiting for WAL file: 000100AD000B WAL file path: /var/pgsql/data/archive//000100AD000B Restoring to: pg_xlog/RECOVERYXLOG Sleep interval: 2 seconds Max wait interval:0 forever Command for restore: cp "/var/pgsql/data/archive//000100AD000B" "pg_xlog/RECOVERYXLOG" Keep archive history: and later running restore: OK - check tables from psql: postgres=# \d List of relations Schema | Name | Type | Owner ++---+-- public | test22 | table | postgres public | test23 | table | postgres public | test25 | table | postgres public | test26 | table | postgres <===this table was created at 17:01:53 and should not be restored (4 rows) postgres=# select count(1) from test26; count - 260 (1 row) I am new to PITR, can you please advise if this is a bug and any work around? regards -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Enable WAL Archive in Replication server
Thanks so much. On 12 Aug 2013, at 6:45 AM, Michael Paquier wrote: > On Mon, Aug 12, 2013 at 1:53 AM, ascot.m...@gmail.com > wrote: >> I want to enable WAL archive on the replica, I am advised that WAL archive >> can only be enabled on the Master, can you please advise if this is a bug >> and any workaround available? > Archiving a WAL file is only done once its generation has been > completed by a master server. A standby node only replays WAL files, > copying them from the indicated archive folder with restore_command to > its local pg_xlog folder when necessary, and performs no archive > actions. > http://www.postgresql.org/docs/devel/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL > -- > Michael -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Recovery.conf PITR by recovery_target_time
Thanks very much! I guess you should use the same machine to carry out the PITR while I am using two physical machines. I will try it again. On 13 Aug 2013, at 12:11 PM, Michael Paquier wrote: > It looks that you are missing something. Similarly to what you did, here is > an example of PITR using a base backup: > 1) Here is my master node doing some archiving: > $ psql -c 'show archive_command' -p 5432 > archive_command > > cp -i %p /home/mpaquier/bin/pgsql/archive/node_5432/%f > (1 row) > 2) Creating data folder of new node using a base backup: > pg_basebackup -D ~/bin/pgsql/slave -p 5432 > echo "port = 5433" >> ~/bin/pgsql/slave/postgresql.conf > This node will run with port 5433. > 3) Creating some data: > $ psql -c 'CREATE TABLE aa AS SELECT generate_series(1,100) AS a' -p 5432 > SELECT 100 > $ date > 2013-08-12 19:47:33 GMT > $ psql -c 'CREATE TABLE bb AS SELECT generate_series(1,100) AS a' -p 5432 > SELECT 100 > Similarly to what you did, after doing the recovery table bb will not exist > on the node recovered with PITR. > 4) Preparing recovery for slave: > echo "restore_command = 'cp -i /home/mpaquier/bin/pgsql/archive/node_5432/%f > %p'" > ~/bin/pgsql/slave/recovery.conf > echo "recovery_target_time = '2013-08-12 19:47:33 GMT'" >> > ~/bin/pgsql/slave/recovery.conf > 5) Time to perform the PITR: > $ tail -n3 ~/bin/pgsql/slave/pg_log/postgresql-2013-08-12_195441.log > LOG: recovery stopping before commit of transaction 1305, time 2013-08-12 > 19:48:22.436774+00 > LOG: recovery has paused > HINT: Execute pg_xlog_replay_resume() to continue. > Note that in this case the recovery has stopped such as you can check the > status of the node before resuming its activity (you can as well enforce the > resume if you wish) > 6) Now let's check that the node is in a correct state: > $ psql -p 5433 > psql (9.3beta2) > Type "help" for help. > > mpaquier=# \d > List of relations > Schema | Name | Type | Owner > +--+---+-- > public | aa | table | mpaquier > (1 row) > And only table aa exists. > > Here is more input after resume xlog replay. > mpaquier=# create table bb (a int); -- Node is still in read-only mode > ERROR: 25006: cannot execute CREATE TABLE in a read-only transaction > LOCATION: PreventCommandIfReadOnly, utility.c:270 > mpaquier=# select pg_xlog_replay_resume(); > pg_xlog_replay_resume > --- > > (1 row) > mpaquier=# create table bb (a int); > CREATE TABLE > > Et voila! > > On Mon, Aug 12, 2013 at 7:34 PM, ascot.m...@gmail.com > wrote: > > - check tables from psql: > > postgres=# select count(1) from test26; > > count > > - > > 260 > > (1 row) > Perhaps you are connecting to the master node and not the node that has been > recovered when querying that? > > Regards, > -- > Michael
