[GENERAL] Install PostgreSQL 9.2.4 to IBM Power System ppc64
Hi, I have two IBM Power System servers, the architecture is ppc64, the PostgreSQL on RHEL6 for IBM Power is version 8.4 but I need to install 9.2.4 as I need to use the PostgreSQL replication features. I am new to PostgreSQL on ppc64, can any one advise me where to get the proper 9.2.4 installation package for ppc64? or please advise me the steps to compile from the 9.2.4 source. Regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to build my own 9.2.4 installer package for IBM Power System ppc64
Hi, I have managed to install 9.2.4 to an IBM Power System server ppc64 by compiling pg 9.2.4 from source as I cannot find the 9.2.4 installer package for ppc64. Can anyone advise me how to build my own installer package for ppc64 (e.g. tools, steps)? since I have more than one ppc64 server and I want to avoid the compile-from-source approach. regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to build my own 9.2.4 installer package for IBM Power System ppc64
Hi John, On 17 Apr 2013, at 11:30 AM, John R Pierce wrote: > on AIX, I build mine to run in /opt/$MYGROUP/pgsql/9.2 and I just tar it up > and distribute it as a tarball to my operations people. I'd previously asked > them if they wanted it as a system package, and their response was 'why > bother?'. I throw in a script that adds it as a service (via /etc/init.d > & rc3.d, I'm too lazy to figure out the new AIX 6.1 service manager). Thanks, would you mind to share your script and steps that add pg as a service (I am using IBM ppc64 Linux)? regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] vacuum_cost_delay and autovacuum_cost_delay
Hi, if I change the value of "vacuum_cost_delay", what is the impact of it on autovacuum side? regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgloader error : permission denied to set parameter "lc_messages"
Hi, I am trying to load CSV files into postgresql via pgloader, it returned the following error, any suggestions to fix it? I have a line "lc_messages = C" in my pgloader.conf, would this line cause the problem? regards pgloader ERRORpermission denied to set parameter "lc_messages" pgloader ERRORpermission denied to set parameter "lc_messages" Traceback (most recent call last): File "/usr/bin/pgloader", line 779, in ret = load_data() File "/usr/bin/pgloader", line 687, in load_data (started[s], finished[s]), summary[s]) File "/usr/lib/pymodules/python2.7/pgloader/pgloader.py", line 142, in __init__ self._read_conf(name, config, db) File "/usr/lib/pymodules/python2.7/pgloader/pgloader.py", line 328, in _read_conf self.columns = self.db.get_all_columns(self.table) File "/usr/lib/pymodules/python2.7/pgloader/db.py", line 153, in get_all_columns self.reset() File "/usr/lib/pymodules/python2.7/pgloader/db.py", line 189, in reset self.set_pg_options() File "/usr/lib/pymodules/python2.7/pgloader/db.py", line 117, in set_pg_options raise PGLoader_Error, e pgloader.tools.PGLoader_Error: permission denied to set parameter "lc_messages" -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_stat_replication became empty suddenly
Hi,
I am doing some stress tests to a pair of PG servers to monitor the
pg_stat_replication, during the test, the pg_stat_replication suddenly became
empty.
PG version: 9.2.4
O/S: Ubuntu: 12.04
Since I need to monitor the replication lag from time to time, if the
pg_stat_replication becomes empty, the lag calculation in the slave will be
wrong.
Please advise if this is a bug.
regards
How to reproduce:
session 1: Master server - try to insert a large number of records into a test
table
postgres=# drop table test;CREATE TABLE test (id INTEGER PRIMARY KEY); INSERT
INTO test VALUES (generate_series(1,1)); EXPLAIN ANALYZE SELECT
COUNT(*) FROM test;
2) session 2: Master server - check the byte_lag from time to time
postgres=# SELECT
sent_offset - (
replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ) AS byte_lag
FROM (
SELECT
client_addr,
('x' || lpad(split_part(sent_location, '/', 1), 8,
'0'))::bit(32)::bigint AS sent_xlog,
('x' || lpad(split_part(replay_location, '/', 1), 8,
'0'))::bit(32)::bigint AS replay_xlog,
('x' || lpad(split_part(sent_location, '/', 2), 8,
'0'))::bit(32)::bigint AS sent_offset,
('x' || lpad(split_part(replay_location, '/', 2), 8,
'0'))::bit(32)::bigint AS replay_offset
FROM pg_stat_replication
) AS s;
byte_lag
--
2097216
(1 row)
postgres=# SELECT
sent_offset - (
replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ) AS byte_lag
FROM (
SELECT
client_addr,
('x' || lpad(split_part(sent_location, '/', 1), 8,
'0'))::bit(32)::bigint AS sent_xlog,
('x' || lpad(split_part(replay_location, '/', 1), 8,
'0'))::bit(32)::bigint AS replay_xlog,
('x' || lpad(split_part(sent_location, '/', 2), 8,
'0'))::bit(32)::bigint AS sent_offset,
('x' || lpad(split_part(replay_location, '/', 2), 8,
'0'))::bit(32)::bigint AS replay_offset
FROM pg_stat_replication
) AS s;
byte_lag
--
(0 rows)
3) session 3: Slave server -
postgres=# SELECT CASE WHEN pg_last_xlog_receive_location() =
pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() -
pg_last_xact_replay_timestamp()) END AS log_delay;
log_delay
---
0
(1 row)
postgres=# SELECT CASE WHEN pg_last_xlog_receive_location() =
pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() -
pg_last_xact_replay_timestamp()) END AS log_delay;
log_delay
---
4.873282
(1 row)
.
.
.
postgres=# SELECT CASE WHEN pg_last_xlog_receive_location() =
pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() -
pg_last_xact_replay_timestamp()) END AS log_delay;
log_delay
-
4070.325329
(1 row)
Re: [GENERAL] pg_stat_replication became empty suddenly
Hi, I found the problem should be because I tried to clean RAM cache in the slave by running "sync; echo 3 > /proc/sys/vm/drop_caches' that caused the "receiver" of slave gone away. ps -ef | grep receiver postgres 6182 6178 0 12:11 ?00:00:06 postgres: wal receiver process streaming D/FB8DA000 sync; echo 3 > /proc/sys/vm/drop_caches ps -ef | grep receiver root 8804 30447 0 12:29 pts/200:00:00 grep --color=auto receiver regards On 6 Aug 2013, at 10:44 AM, [email protected] wrote: > Hi, > > I am doing some stress tests to a pair of PG servers to monitor the > pg_stat_replication, during the test, the pg_stat_replication suddenly became > empty. > > PG version: 9.2.4 > O/S: Ubuntu: 12.04 > > Since I need to monitor the replication lag from time to time, if the > pg_stat_replication becomes empty, the lag calculation in the slave will be > wrong. > Please advise if this is a bug. > > regards > > > > > How to reproduce: > > session 1: Master server - try to insert a large number of records into a > test table > postgres=# drop table test;CREATE TABLE test (id INTEGER PRIMARY KEY); INSERT > INTO test VALUES (generate_series(1,1)); EXPLAIN ANALYZE SELECT > COUNT(*) FROM test; > > 2) session 2: Master server - check the byte_lag from time to time > postgres=# SELECT > sent_offset - ( > replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ) AS byte_lag > FROM ( > SELECT > client_addr, > ('x' || lpad(split_part(sent_location, '/', 1), 8, > '0'))::bit(32)::bigint AS sent_xlog, > ('x' || lpad(split_part(replay_location, '/', 1), 8, > '0'))::bit(32)::bigint AS replay_xlog, > ('x' || lpad(split_part(sent_location, '/', 2), 8, > '0'))::bit(32)::bigint AS sent_offset, > ('x' || lpad(split_part(replay_location, '/', 2), 8, > '0'))::bit(32)::bigint AS replay_offset > FROM pg_stat_replication > ) AS s; > byte_lag > -- > 2097216 > (1 row) > > postgres=# SELECT > > sent_offset - ( > replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ) AS byte_lag > FROM ( > SELECT > client_addr, > ('x' || lpad(split_part(sent_location, '/', 1), 8, > '0'))::bit(32)::bigint AS sent_xlog, > ('x' || lpad(split_part(replay_location, '/', 1), 8, > '0'))::bit(32)::bigint AS replay_xlog, > ('x' || lpad(split_part(sent_location, '/', 2), 8, > '0'))::bit(32)::bigint AS sent_offset, > ('x' || lpad(split_part(replay_location, '/', 2), 8, > '0'))::bit(32)::bigint AS replay_offset > FROM pg_stat_replication > ) AS s; > byte_lag > -- > (0 rows) > > > 3) session 3: Slave server - > postgres=# SELECT CASE WHEN pg_last_xlog_receive_location() = > pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - > pg_last_xact_replay_timestamp()) END AS log_delay; > log_delay > --- > 0 > (1 row) > > postgres=# SELECT CASE WHEN pg_last_xlog_receive_location() = > pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - > pg_last_xact_replay_timestamp()) END AS log_delay; > log_delay > --- > 4.873282 > (1 row) > > . > . > . > postgres=# SELECT CASE WHEN pg_last_xlog_receive_location() = > pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - > pg_last_xact_replay_timestamp()) END AS log_delay; > log_delay > - > 4070.325329 > (1 row) > >
[GENERAL] psql: FATAL: the database system is starting up
Hi, I just setup the replication in the slave again, when trying to use psql, I could not get the psql command prompt but got "psql: FATAL: the database system is starting up" from it. PG: 9.2.4 Below is the log from the the slave: LOG: database system was shut down in recovery at 2013-08-06 18:34:44 LOG: entering standby mode LOG: consistent recovery state reached at 1C/9A0F9CF0 LOG: record with zero length at 1C/9A0F9CF0 LOG: streaming replication successfully connected to primary FATAL: the database system is starting up I am new to PG replication, please help. regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql: FATAL: the database system is starting up
Thanks, there was a typo of the line "hot_standby", it works now, thanks again. On 6 Aug 2013, at 6:52 PM, Haribabu kommi wrote: > > On 06 August 2013 16:13 ascot.moss wrote >> Hi, > >> I just setup the replication in the slave again, when trying to use psql, I >> could not get the psql command prompt but got "psql: FATAL: the database >> system is starting up" from it. > >> PG: 9.2.4 > >> Below is the log from the the slave: >> LOG: database system was shut down in recovery at 2013-08-06 18:34:44 >> LOG: entering standby mode >> LOG: consistent recovery state reached at 1C/9A0F9CF0 >> LOG: record with zero length at 1C/9A0F9CF0 >> LOG: streaming replication successfully connected to primary >> FATAL: the database system is starting up > >> I am new to PG replication, please help. > > There is a configuration parameter "hot_standby" is set as on or not? > This allows the queries during recovery. > > Regards, > Hari babu. > -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_stat_replication became empty suddenly
000120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed LOG: streaming replication successfully connected to primary FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100120005 has already been removed On 6 Aug 2013, at 12:39 PM, [email protected] wrote: > Hi, > > I found the problem should be because I tried to clean RAM cache in the slave > by running "sync; echo 3 > /proc/sys/vm/drop_caches' > that caused the "receiver" of slave gone away. > > ps -ef | grep receiver > postgres 6182 6178 0 12:11 ?00:00:06 postgres: wal receiver > process streaming D/FB8DA000 > sync; echo 3 > /proc/sys/vm/drop_caches > ps -ef | grep receiver > root 8804 30447 0 12:29 pts/200:00:00 grep --color=auto receiver > > regards > > On 6 Aug 2013, at 10:44 AM, [email protected] wrote: > >> Hi, >> >> I am doing some stress te
Re: [GENERAL] pg_stat_replication became empty suddenly
Thanks. I increased the wal_keep_segments and it works well now. On 7 Aug 2013, at 12:43 AM, Jerry Sievers wrote: > "[email protected]" writes: > >> Hi, >> >> I just tried another round of tests, without running "sync; echo 3 > >> /proc/sys/vm/drop_caches', >> still got the same error, following FATAL errors are found in pg_log >> (slave), can anyone please advise how to resolve >> this error? >> >> regards >> >> LOG: entering standby mode >> LOG: consistent recovery state reached at 11/42000318 >> LOG: redo starts at 11/42000280 >> LOG: invalid record length at 11/42000318 >> LOG: database system is ready to accept read only connections >> LOG: streaming replication successfully connected to primary >> FATAL: could not send data to WAL stream: server closed the connection >> unexpectedly >> This probably means the server terminated abnormally >> before or while processing the request. >> LOG: unexpected pageaddr 10/D2EC in log file 18, segment 5, offset >> 15466496 >> LOG: streaming replication successfully connected to primary >> FATAL: could not receive data from WAL stream: FATAL: requested WAL >> segment 000100120005 has already >> been removed >> LOG: streaming replication successfully connected to primary >> FATAL: could not receive data from WAL stream: FATAL: requested WAL >> segment 000100120005 has already > > Raise wal_keep_segments on your master configs ,and HUP and/or start > your standby a lot sooner after it's reloaded. > > >> > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: [email protected] > p: 312.241.7800 -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to find transaction ID
Hi, I am trying some restore tools, can you advise how to find the latest transaction ID in PostgreSQL and the transaction ID at a particular "Point-In-Time"? regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recovery.conf and PITR
Hi, I am trying PITR in a test machine (same PG version 9.2.4 same O/S Ubuntu 12.04 64 bit). All archived WAL files are shipped and saved in /var/pgsql/data/archive, the latest transaction txid of them is 75666. I want to recover PG at a point of time that if XIDs are equal or smaller than '75634' so I have the following recovery.conf (only two lines): restore_command = 'cp /var/pgsql/data/archive/%f %p' recovery_target_xid = '75634' After the restart of PG, the recovery.conf is processed and it is renamed to recovery.done. However it restored all (75666) instead of '75634'. postgres=# select txid_current(); txid_current -- 75666 (1 row) Can you please advise? regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] archive folder housekeeping
Hi, I have enabled archive in PG (v 9.2.4): archive_mode = on archive_command = 'test ! -f /usr/local/pgsql/data/archive/%f && cp %p /usr/local/pgsql/data/archive/%f' I know that pg_xlog folder is maintained by PostgreSQL automatically, when the pg_xlog folder hits to certain limit (pg_xlog is full), it will automatically archive old log files into the archive folder. Is the parameter wal_keep_segments used to control this limit? On the other hand, will PostgreSQL also automatically maintain the archive folder by itself or I need to do some housekeeping job to maintain it from time to time? Is there any PG manual command available to remove archived files by (archive) date/time? Can you please advise? regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovery.conf and PITR
On 9 Aug 2013, at 7:09 PM, Luca Ferrari wrote: > Uhm...I guess the problem is not about the txid being included or not: > the recovery target was 75634 and the transaction 75666 appeared, so > the problem seems to be an out-of-order commit of the transactions. In > such case making the inclusive false will not cause 75666 to appear if > it has committed before the target xid, or am I wrong? Hi, Is there a way to query the master (it is up and running) about the actual commit sequence by transaction IDs? regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recovery.conf and PITR by recovery_target_time
Hi, I am trying another way to test PITR: by recovery_target_time. The test machine has the same PG version 9.2.4 and same O/S Ubuntu 12.04 64 bit.All archived WAL files are shipped and saved in /var/pgsql/data/archive, the latest time stamp of them is "2013-08-09 19:30:01", the full hot backup time is at '2013-08-09 16:47:12'. Case 1) I want to recover PG to the state before 18:03:02 that there were 6 tables deleted Case 2) Hope to recover PG to the point of time right before table TEST8 was created Transactions in master: 16:45:01(create 4 test tables : test1, test2, test3, test4) 16:47:12(FULL HOT BACKUP) 17:50:22postgres=# CREATE TABLE test5 (id INTEGER PRIMARY KEY); INSERT INTO test5 VALUES (generate_series(1,400)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test5; 17:57:13postgres=# CREATE TABLE test6 (id INTEGER PRIMARY KEY); INSERT INTO test6 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test6; postgres=# \d List of relations Schema | Name | Type | Owner +---+---+-- public | test1 | table | postgres (created before full hot backup) public | test2 | table | postgres (created before full hot backup) public | test3 | table | postgres (created before full hot backup) public | test4 | table | postgres (created before full hot backup) public | test5 | table | postgres public | test6 | table | postgres 18:03:02postgres=# drop table test1; DROP TABLE postgres=# drop table test2; DROP TABLE postgres=# drop table test3; DROP TABLE postgres=# drop table test4; DROP TABLE postgres=# drop table test5; DROP TABLE postgres=# drop table test6; DROP TABLE postgres=# commit; WARNING: there is no transaction in progress COMMIT 18:04:34postgres=# CREATE TABLE test7 (id INTEGER PRIMARY KEY); INSERT INTO test7 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test7; 18:11:31postgres=# CREATE TABLE test8 (id INTEGER PRIMARY KEY); INSERT INTO test8 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test8; postgres=# CREATE TABLE test9 (id INTEGER PRIMARY KEY); INSERT INTO test9 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test9; postgres=# CREATE TABLE test10 (id INTEGER PRIMARY KEY); INSERT INTO test10 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test10; 19:26:18postgres=# vacuum; VACUUM postgres=# begin; INSERT INTO test10 VALUES (generate_series(202,302));commit; end; BEGIN INSERT 0 101 COMMIT WARNING: there is no transaction in progress COMMIT postgres=# CREATE TABLE test11 (id INTEGER PRIMARY KEY); INSERT INTO test11 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test11; 19:30:01(ship the WAL file to test machine) CASE-1: '2013-08-09 17:57:55' (only 3 lines in recovery.conf) restore_command = 'cp /var/pgsql/data/archive/%f %p' recovery_target_time = '2013-08-09 17:57:55' recovery_target_inclusive = false Result: LOG: starting point-in-time recovery to 2013-08-09 17:57:55 LOG: restored log file "0001006F0066" from archive LOG: redo starts at 6F/6620 LOG: recovery stopping before commit of transaction 75891, time 2013-08-09 18:07:09.547682+08 LOG: redo done at 6F/66003DF0 FATAL: requested recovery stop point is before consistent recovery point LOG: startup process (PID 15729) exited with exit code 1 LOG: terminating any other active server processes [1]+ Exit 1 ... CASE-2: '2013-08-09 18:06:01' (only 3 lines in recovery.conf) restore_command = 'cp /var/pgsql/data/archive/%f %p' recovery_target_time = '2013-08-09 18:06:01' recovery_target_inclusive = false Result: LOG: starting point-in-time recovery to 2013-08-09 18:06:01 LOG: restored log file "0001006F00B0" from archive LOG: restored log file "0001006F009B" from archive LOG: redo start
Re: [GENERAL] Recovery.conf and PITR by recovery_target_time
hi
>> 16:47:12
SELECT pg_start_backup('hot_backup');
"tar cfP" the PG "data" folder
SELECT pg_stop_backup();
regards
On 9 Aug 2013, at 9:55 PM, Albe Laurenz wrote:
> [email protected] wrote:
>> I am trying another way to test PITR: by recovery_target_time.
>>
>> The test machine has the same PG version 9.2.4 and same O/S Ubuntu 12.04 64
>> bit.All archived WAL
>> files are shipped and saved in /var/pgsql/data/archive, the latest time
>> stamp of them is "2013-08-09
>> 19:30:01", the full hot backup time is at '2013-08-09 16:47:12'.
>>
>> Case 1) I want to recover PG to the state before 18:03:02 that there were 6
>> tables deleted
>> Case 2) Hope to recover PG to the point of time right before table TEST8 was
>> created
>>
>> Transactions in master:
>> 16:45:01 (create 4 test tables : test1, test2, test3, test4)
>> 16:47:12 (FULL HOT BACKUP)
>> 17:50:22 postgres=# CREATE TABLE test5 (id INTEGER PRIMARY KEY); INSERT
>> INTO test5 VALUES
>> (generate_series(1,400)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test5;
>> 17:57:13 postgres=# CREATE TABLE test6 (id INTEGER PRIMARY KEY); INSERT
>> INTO test6 VALUES
>> (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test6;
>> postgres=# \d
>> List of relations
>> Schema | Name | Type | Owner
>> +---+---+--
>> public | test1 | table | postgres (created before full
>> hot backup)
>> public | test2 | table | postgres (created before full
>> hot backup)
>> public | test3 | table | postgres (created before full
>> hot backup)
>> public | test4 | table | postgres (created before full
>> hot backup)
>> public | test5 | table | postgres
>> public | test6 | table | postgres
>> 18:03:02 postgres=# drop table test1; DROP TABLE
>> postgres=# drop table test2; DROP TABLE
>> postgres=# drop table test3; DROP TABLE
>> postgres=# drop table test4; DROP TABLE
>> postgres=# drop table test5; DROP TABLE
>> postgres=# drop table test6; DROP TABLE
>> postgres=# commit; WARNING: there is no transaction in
>> progress COMMIT
>> 18:04:34 postgres=# CREATE TABLE test7 (id INTEGER PRIMARY KEY); INSERT
>> INTO test7 VALUES
>> (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test7;
>> 18:11:31 postgres=# CREATE TABLE test8 (id INTEGER PRIMARY KEY); INSERT
>> INTO test8 VALUES
>> (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test8;
>> postgres=# CREATE TABLE test9 (id INTEGER PRIMARY KEY);
>> INSERT INTO test9 VALUES
>> (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test9;
>> postgres=# CREATE TABLE test10 (id INTEGER PRIMARY
>> KEY); INSERT INTO test10 VALUES
>> (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test10;
>> 19:26:18 postgres=# vacuum;
>> VACUUM
>> postgres=# begin; INSERT INTO test10 VALUES
>> (generate_series(202,302));commit; end; BEGIN INSERT 0 101
>> COMMIT WARNING: there is no
>> transaction in progress COMMIT
>> postgres=# CREATE TABLE test11 (id INTEGER PRIMARY
>> KEY); INSERT INTO test11 VALUES
>> (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test11;
>> 19:30:01 (ship the WAL file to test machine)
>>
>>
>>
>>
>> CASE-1: '2013-08-09 17:57:55' (only 3 lines in recovery.conf)
>> restore_command = 'cp /var/pgsql/data/archive/%f %p'
>> recovery_target_time = '2013-08-09 17:57:55'
>> recovery_target_inclusive = false
>> Result:
>> LOG: starting point-in-time recovery to 2013-08-09
>> 17:57:55
>> LOG: restored log file "0001006F0066" from
>> archive
>> LOG: redo starts at 6F/6620
>> LOG: recovery stopping before commit of transaction
>> 75891, time 2013-08-09
>> 18:07:09.547682+08
>> LOG: redo done at 6F/66003DF0
>>
[GENERAL] Replication delay
Hi, I have a pair of PG servers, a master and a replica, all read-write queries are handled by the master, read-only ones are by the replica. >From time to time the replica itself is too busy, all read-only queries will >get inconsistent results because of replication lag, sometimes it can be >longer than 6 minutes. I am thinking to add multiple replicas to off-load >read-only queries, can you please suggest a way to monitor and failover the >read-only query when the replication lag in a replica is more than 5 minutes? regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] replication server: LOG: invalid magic number 0000 in log file 169, segment 77, offset 4325376
Hi, I found 'LOG: invalid magic number in log file 169, segment 77, offset 4325376" from the replica's log: LOG: entering standby mode LOG: redo starts at A8/BE81B200 LOG: consistent recovery state reached at A9/4CF8 LOG: database system is ready to accept read only connections LOG: invalid magic number in log file 169, segment 77, offset 4325376 LOG: streaming replication successfully connected to primary Can you please advise what it means? can it be ignored? regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Enable WAL Archive in Replication server
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 -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] replication server: LOG: invalid magic number 0000 in log file 169, segment 77, offset 4325376
Thanks so much. On 11 Aug 2013, at 7:36 PM, Jov wrote: > This means the slave meet the end of the WAL when it replay the WAL > files/records.The message level is "LOG",so it can be safely ignored. > > Jov > blog: http:amutu.com/blog > > > 2013/8/11 [email protected] > Hi, > > I found 'LOG: invalid magic number in log file 169, segment 77, offset > 4325376" from the replica's log: > > LOG: entering standby mode > LOG: redo starts at A8/BE81B200 > LOG: consistent recovery state reached at A9/4CF8 > LOG: database system is ready to accept read only connections > LOG: invalid magic number in log file 169, segment 77, offset 4325376 > LOG: streaming replication successfully connected to primary > > Can you please advise what it means? can it be ignored? > > regards > > > > -- > Sent via pgsql-general mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > >
Re: [GENERAL] Replication delay
Thanks so much. On 11 Aug 2013, at 9:50 PM, Michael Paquier wrote: > On Sun, Aug 11, 2013 at 5:51 AM, [email protected] > wrote: >> Hi, >> >> I have a pair of PG servers, a master and a replica, all read-write queries >> are handled by the master, read-only ones are by the replica. >> >> From time to time the replica itself is too busy, all read-only queries will >> get inconsistent results because of replication lag, sometimes it can be >> longer than 6 minutes. I am thinking to add multiple replicas to off-load >> read-only queries, can you please suggest a way to monitor and failover the >> read-only query when the replication lag in a replica is more than 5 minutes? > I assume that you could use pgpool for that. It has some monitoring > features for replication delay and it can do read-only load balancing > among several servers. You also shouldn't need to change your > application. > Have a look at its documentation to make an opinion: > http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html > > Regards, > -- > Michael -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SSL or Tunnelling for Streaming Replication
Hi, I am planning Streaming Replication to a new remote server, can you please suggest how to set up data encryption by SSL or tunnelling in Postgresql? regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to find out unused indexes?
Hi, Can you please advise how to find out all unused indexes in PG? regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find out unused indexes?
Thanks. how about the case as follows? idx_scan = 0 and idx_tup_read = 0 and idx_tup_fetch = 0, but idx_blks_read is not 0 and idx_blks_hit is not 0 ? regards On 3 Sep 2013, at 5:31 PM, Abdul Sayeed wrote: > Hi, > > Below query can be handy to find unused indexes. > > select indexrelname from pg_stat_user_indexes where idx_scan = 0 and > idx_tup_read = 0 and idx_tup_fetch = 0 ; > > Hope it helps. > > > > Thanks & Regards, > Abdul Sayeed > EnterpriseDB Corporation - RDBA Team > The Postgres Database Company > > Office: +1-732-931-7033/+91-20-305-89510 > Fax: +91-20-30589502 > Skype: abdul.sayeed24 > > Website: www.enterprisedb.com > EnterpriseDB Blog : http://blogs.enterprisedb.com > Follow us on Twitter : http://www.twitter.com/enterprisedb > > This e-mail message (and any attachment) is intended for the use of the > individual or entity to whom it is addressed. This message contains > information from EnterpriseDB Corporation that may be privileged, > confidential, or exempt from disclosure under applicable law. If you are not > the intended recipient or authorized to receive this for the intended > recipient, any use, dissemination, distribution,retention, archiving, or > copying of this communication is strictly prohibited. If you have received > this e-mail in error, please notify the sender immediately by reply e-mail > and delete this message. > > > On Tue, Sep 3, 2013 at 12:14 PM, Raghavendra > wrote: > On Tue, Sep 3, 2013 at 11:36 AM, [email protected] > wrote: > Hi, > > Can you please advise how to find out all unused indexes in PG? > > regards > > Below wiki link should help; under "Unused Index" section: > > http://wiki.postgresql.org/wiki/Index_Maintenance > > --- > Regards, > Raghavendra > EnterpriseDB Corporation > Blog: http://raghavt.blogspot.com/ > > > -- > Sent via pgsql-general mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > >
[GENERAL] How to check if any WAL file is missing in archive folder
Hi, I am planing to backup archived WAL files from master to another machine, is there a way to check and make sure the backup archive file are all good and no any file missing or corrupted? regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question About WAL filename and its time stamp
Hi, From the pg_xlog folder, I found some files with interesting time stamps: older file names with newer timestamps, can you please advise why? Set 1: How come 00040F49008D is 10 minutes newer than 00040F49008E? -rw--- 1 111 115 16777216 Sep 4 15:28 00040F49008C -rw--- 1 111 115 16777216 Sep 4 15:27 00040F49008D <=== -rw--- 1 111 115 16777216 Sep 4 15:17 00040F49008E < -rw--- 1 111 115 16777216 Sep 4 15:26 00040F49008F -rw--- 1 111 115 16777216 Sep 4 15:27 00040F490090 Set 2: why files, 00040F4800FD, 00040F4800FE and 00040F49, are not reused? 1) -rw--- 1 postgres postgres 16777216 Sep 4 23:07 00040F4800FA 2) -rw--- 1 postgres postgres 16777216 Sep 4 23:08 00040F4800FB 3) -rw--- 1 postgres postgres 16777216 Sep 4 23:09 00040F4800FC <=== 4) -rw--- 1 postgres postgres 16777216 Sep 4 14:47 00040F4800FD < 5) -rw--- 1 postgres postgres 16777216 Sep 4 14:46 00040F4800FE 6) -rw--- 1 postgres postgres 16777216 Sep 4 14:46 00040F49 regards
[GENERAL] invalid resource manager ID in primary checkpoint record
Hi, For special testing reason, I am trying to restore PG from a backup that the basebase is from Standby and WAL files are from Master. During recovery phase, for every WAL file process, it returned 'invalid resource manager ID in primary checkpoint record' and paused, I had to manually run "$PGBIN/pg_resetxlog -f $PG_DATA" many times, is there a way to set PG to ignore this kind of errors. LOG: database system was shut down at 2013-09-10 03:06:29 : starting archive recovery LOG: restored log file "000100B900CA" from archive LOG: invalid resource manager ID in primary checkpoint record LOG: invalid secondary checkpoint link in control file PANIC: could not locate a valid checkpoint record LOG: startup process (PID 17969) was terminated by signal 6: Aborted LOG: aborting startup due to startup process failure . LOG: restored log file "000100B900CB" from archive LOG: invalid resource manager ID in primary checkpoint record LOG: invalid secondary checkpoint link in control file PANIC: could not locate a valid checkpoint record LOG: startup process (PID 17981) was terminated by signal 6: Aborted LOG: aborting startup due to startup process failure . LOG: restored log file "000100B900CC" from archive . LOG: restored log file "000100B900CD" from archive Please advise. regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] invalid resource manager ID in primary checkpoint record
Hi, any idea? can you please advise? On 10 Sep 2013, at 3:22 AM, [email protected] wrote: > Hi, > > For special testing reason, I am trying to restore PG from a backup that the > basebase is from Standby and WAL files are from Master. During recovery > phase, for every WAL file process, it returned 'invalid resource manager ID > in primary checkpoint record' and paused, I had to manually run > "$PGBIN/pg_resetxlog -f $PG_DATA" many times, is there a way to set PG to > ignore this kind of errors. > > LOG: database system was shut down at 2013-09-10 03:06:29 : starting > archive recovery > LOG: restored log file "000100B900CA" from archive > LOG: invalid resource manager ID in primary checkpoint record > LOG: invalid secondary checkpoint link in control file > PANIC: could not locate a valid checkpoint record > LOG: startup process (PID 17969) was terminated by signal 6: Aborted > LOG: aborting startup due to startup process failure > . > LOG: restored log file "000100B900CB" from archive > LOG: invalid resource manager ID in primary checkpoint record > LOG: invalid secondary checkpoint link in control file > PANIC: could not locate a valid checkpoint record > LOG: startup process (PID 17981) was terminated by signal 6: Aborted > LOG: aborting startup due to startup process failure > . > LOG: restored log file "000100B900CC" from archive > . > LOG: restored log file "000100B900CD" from archive > > > Please advise. > regards > > > > -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] fsync and wal_sync_method
Hi, I am using pg 9.2.4 and ubuntu 12.04 to set up a pair of pg-Master and pg-Slave with streaming replication. The archive_command is enabled and the "rsync" is used in pg-Master to save all its archived WAL files to the 3rd machine for backup purpose, by default, both fsync and wal_sync_method are commented out in postgresql.conf: archive_command = '(cp %p /usr/pg_arcxlog/%f && rsync -aq %p pg@pg_slave:/usr/pg_arcxlog/%f' #fsync = on # turns forced synchronization on or off #wal_sync_method = fsync# the default is the first option Thus I think the latest WAL might not be flushed to disk from time to time in pg-Master, therefore the WAL saved in the 3rd server might not contain the latest WAL. In order to avoid possible data lost, should 'fsync" be turned ON in pg-Master and pg-Slave? if yes what would be the best settings of "wal_sync_method" for these two servers respectively? Please advise. regards -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to failover from Primary to Standby and Set the old Primary as a new Standby
Hi,
I use PG 9.2.4 with streaming replication. What will be the manual procedure
to failover from Primary to Standby and Set the old Primary as a new standby?
step 1: standby's recovery.conf :
# Specifies a trigger file whose presence should cause streaming replication to
end (i.e., failover).
trigger_file = '/var/lib/postgresql/main/trigger'
step 2: To trigger a smart failover, create a trigger file containing the word
smart, or just create it and leave it empty. (e.g.
vi /var/lib/postgresql/main/trigger
smart
step 3: wait until the failover is completed, the server is brought up after
applying all WAL files available in the archive.
How to check if the failover is completed and the new Primary is ready?
step 4: if the failover is done
Do I need to edit the new primary's postgresql.conf and restart postgresql?
e.g. comment out the hot_standy = on
step 5: how to quickly set the old primary as a new standby?
SELECT pg_start_backup('replbackup');
tar cfP /home/postgres/pg_backup.tar $PG_DATA
SELECT pg_stop_backup();
send /home/postgres/pg_backup.tar to the old primary
unzip the tar file to $PG_DATA & delete postmaster.pid
create the recovery.conf
edit the postgresql.conf to enable "hot_standby = on"
start postgresql
if the PG_DATA has 600GB data, is there a quicker way to set the old primary as
a new standby?
regards
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to failover from Primary to Standby and Set the old Primary as a new Standby
Hi, How about the following two areas? step 3: wait until the failover is completed, the server is brought up after applying all WAL files available in the archive. Question 1: How to check if the failover is completed and the new Primary is ready? step 4: if the failover is done Question 2: Do I need to edit the new primary's postgresql.conf and restart postgresql? e.g. comment out the hot_standy = on thanks On 20 Sep 2013, at 4:39 AM, Michael Nolan wrote: > On 9/19/13, John R Pierce wrote: >> On 9/19/2013 1:29 PM, Vick Khera wrote: >>> >>> On Thu, Sep 19, 2013 at 11:31 AM, [email protected] >>> <mailto:[email protected]> >> <mailto:[email protected]>> wrote: >>> >>>I use PG 9.2.4 with streaming replication. What will be the >>>manual procedure to failover from Primary to Standby and Set the >>>old Primary as a new standby? >>> >>> >>> From what I understand, you start over by setting up the old primary >>> as a new standby from scratch. >> >> if you use rsync for the base backup of new master to old, it should go >> fairly quickly as relatively few files should have changed assuming not >> much time has elapsed. > > Of course, before you do anything, you should spend some time figuring > out WHY the old master failed. There could be issues that need to be > resolved before putting it back online, and fixing them could affect > how much work you have to do to get the physical files back in sync. > -- > Mike Nolan > > > -- > Sent via pgsql-general mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
