Re: [GENERAL] CORRUPTION on TOAST table
It seems that it was the Postgres bug on replica, after upgrading minor version to 9.1.21 on replica1, the corruption goes away. Thanks everyone for the help On Tue, Apr 5, 2016 at 1:32 AM, Soni M <diptat...@gmail.com> wrote: > Hello Adrian, thanks for the response. > > master data also located on SAN > > Yes, each replica is it own VM with its own virtual disk/volume as served > up from the same SAN > > Raw disk mappings are a way for ESX to present a SAN volume directly to a > VM instead of creating a virtual disk. > > no unexpected messages detected. > > On Sun, Apr 3, 2016 at 11:23 PM, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 04/02/2016 08:38 PM, Soni M wrote: >> >>> Hello Everyone, >>> >>> We face TOAST table corruption. >>> >>> One master and two streaming replicas. The corruption happen only on >>> both streaming replicas. >>> >>> We did found the corrupted rows. Selecting on this row, return (on both >>> replica) : unexpected chunk number 0 (expected 1) for toast value >>> 1100613112 in pg_toast_112517 >>> selecting this row on master does not return corruption error, but >>> return correct result instead. >>> >>> Previously, dump on a replica return : unexpected chunk number 0 >>> (expected 1) for toast value 3234098599 in pg_toast_112517 (please note >>> the toast value is different) >>> >>> This table size is 343 GB, contain around 206,179,697 live tuples. We >>> found that the corruption happen on the biggest column (this column and >>> its pkey sized around 299 GB total). >>> >>> >> on both replica : >>> fsync NEVER turned off. >>> none unexpected power loss nor OS crash. >>> >>> How can the corruption occurs ? and how can I resolve them ? >>> >> >> Meant to add to previous post. >> >> Do you see anything in the replica Postgres logs that indicate a problem >> with the replication process? >> >> Or any other unexpected messages prior to the point you did the select on >> the replica(s)? >> >> >> >>> Thank so much for the help. >>> >>> Cheers \o/ >>> >>> -- >>> Regards, >>> >>> Soni Maula Harriz >>> >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> > > > > -- > Regards, > > Soni Maula Harriz > -- Regards, Soni Maula Harriz
Re: [GENERAL] CORRUPTION on TOAST table
Hello Adrian, thanks for the response. master data also located on SAN Yes, each replica is it own VM with its own virtual disk/volume as served up from the same SAN Raw disk mappings are a way for ESX to present a SAN volume directly to a VM instead of creating a virtual disk. no unexpected messages detected. On Sun, Apr 3, 2016 at 11:23 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 04/02/2016 08:38 PM, Soni M wrote: > >> Hello Everyone, >> >> We face TOAST table corruption. >> >> One master and two streaming replicas. The corruption happen only on >> both streaming replicas. >> >> We did found the corrupted rows. Selecting on this row, return (on both >> replica) : unexpected chunk number 0 (expected 1) for toast value >> 1100613112 in pg_toast_112517 >> selecting this row on master does not return corruption error, but >> return correct result instead. >> >> Previously, dump on a replica return : unexpected chunk number 0 >> (expected 1) for toast value 3234098599 in pg_toast_112517 (please note >> the toast value is different) >> >> This table size is 343 GB, contain around 206,179,697 live tuples. We >> found that the corruption happen on the biggest column (this column and >> its pkey sized around 299 GB total). >> >> > on both replica : >> fsync NEVER turned off. >> none unexpected power loss nor OS crash. >> >> How can the corruption occurs ? and how can I resolve them ? >> > > Meant to add to previous post. > > Do you see anything in the replica Postgres logs that indicate a problem > with the replication process? > > Or any other unexpected messages prior to the point you did the select on > the replica(s)? > > > >> Thank so much for the help. >> >> Cheers \o/ >> >> -- >> Regards, >> >> Soni Maula Harriz >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Regards, Soni Maula Harriz
Re: [GENERAL] CORRUPTION on TOAST table
Sorry, miss that info. Master 9.1.13, replica1 9.1.13, replica2 9.1.19. Master Red Hat Enterprise Linux Server release 6.5 (Santiago), replica1 Red Hat Enterprise Linux Server release 6.5 (Santiago), replica2 Red Hat Enterprise Linux Server release 6.7 (Santiago). On Sun, Apr 3, 2016 at 10:43 AM, Joshua D. Drake <j...@commandprompt.com> wrote: > > What version of PostgreSQL and which OS? > > > On 04/02/2016 08:38 PM, Soni M wrote: > > >> How can the corruption occurs ? and how can I resolve them ? >> >> Thank so much for the help. >> >> Cheers \o/ >> >> -- >> Regards, >> >> Soni Maula Harriz >> > > > -- > Command Prompt, Inc. http://the.postgres.company/ > +1-503-667-4564 > PostgreSQL Centered full stack support, consulting and development. > Everyone appreciates your honesty, until you are honest with them. > -- Regards, Soni Maula Harriz
[GENERAL] CORRUPTION on TOAST table
Hello Everyone, We face TOAST table corruption. One master and two streaming replicas. The corruption happen only on both streaming replicas. We did found the corrupted rows. Selecting on this row, return (on both replica) : unexpected chunk number 0 (expected 1) for toast value 1100613112 in pg_toast_112517 selecting this row on master does not return corruption error, but return correct result instead. Previously, dump on a replica return : unexpected chunk number 0 (expected 1) for toast value 3234098599 in pg_toast_112517 (please note the toast value is different) This table size is 343 GB, contain around 206,179,697 live tuples. We found that the corruption happen on the biggest column (this column and its pkey sized around 299 GB total). replica1 : ESX 5.5, VM Version 8 Intel(R) Xeon(R) CPU E5649 @ 2.53GHz 8GB RAM Storage – Raw Disk Mapping in ESX from 3PAR 7400 SAN using Fast Class (10k) disk Each volume (single disk as presented by SAN) on the VMs is its own LVM volume. replica2 : ESX 5.5, VM Version 8 Intel(R) Xeon(R) CPU E5-2670 v2 @ 2.50GHz 8GB RAM Raw Disk Mapping in ESX from 3PAR 7400 SAN using Fast Clkass (10k) disk Each volume (single disk as presented by SAN) on the VMs is its own LVM volume. on both replica : fsync NEVER turned off. none unexpected power loss nor OS crash. How can the corruption occurs ? and how can I resolve them ? Thank so much for the help. Cheers \o/ -- Regards, Soni Maula Harriz
Re: [GENERAL] Estimating WAL usage during pg_basebackup
This is hard to tell, but You can get some estimation. 1. You can have WAL rate estimation from pg_xlog/ dir, i.e. How many WAL generated per minutes 2. How long this pg_basebackup will last. Lets say for 3 hours. Then You can multiple values in #1 and #2 to get rough estimation. Hope this would help. On Fri, Oct 31, 2014 at 1:52 AM, Mike Blackwell mike.blackw...@rrd.com wrote: I need to get an idea of how much WAL space will be required during a long (many hours) pg_basebackup over a relatively slow network connection. This is for a server that's not yet running PITR / streaming. Any thoughts? * mike.blackw...@rrd.com* -- Regards, Soni Maula Harriz
Re: [GENERAL] Restart replicated slave procedure
On Sat, Aug 23, 2014 at 2:18 AM, Joseph Kregloh jkreg...@sproutloud.com wrote: On Fri, Aug 22, 2014 at 2:21 PM, Jerry Sievers gsiever...@comcast.net wrote: Joseph Kregloh jkreg...@sproutloud.com writes: Hi, Currently I am doing asynchronous replication from master to slave. Now if I restart the slave it will fall out of sync with the master. Is there a correct procedure or set of steps to avoid this? I am looking for best practices or suggestions. Whenever my slave fell out of sync I would either issue a new pg_base_backup() or set the master to pg_start_backup() do an rsync and stop using pg_stop_backup(). If there is a way to avoid any of that, for example pause replication to hold all the wal files until the replicated slave comes back and then release them once the replicated slave is up. I apologize if this question has already been asked. I did some searching beforehand. See the manual and read up on the 2 GUCs; archive_command and wal_keep_segments. Thanks, i'll read into this some more. wal_keep_segments lets you hold a configurable number of WAL segments back and buy some more time till you have to resync the stand bys. Setting archive_command to '' or something like '/bin/false' lets you delay archiving forever till you change them back again and/or fill whatever file system pg_xlog writes to :-) So disabling the archive_command by setting it to and empty string or /bin/false will effectively pause log shipping? When I re-enable the archive command will it continue where it left of when the archive_command was disabled? AFAIK, disabling archive_command will result on accumulated wal files on xlog dir on master. And when You re-enable the archive_command, it will continue where it left of. It has the status of last archived wal files. Check on PGDATA/pg_xlog/archive_status/ Thanks, -Joseph Kregloh -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Regards, Soni Maula Harriz
Re: [GENERAL] Query planner question
here's the explain analyze result : http://explain.depesz.com/s/Mvv and
http://explain.depesz.com/s/xxF9
it seems that i need to dig more on query planner parameter.
BTW, thanks all for the helps.
On Sat, Aug 23, 2014 at 4:33 PM, Alban Hertroys haram...@gmail.com wrote:
On 23 Aug 2014, at 4:34, Soni M diptat...@gmail.com wrote:
On Fri, Aug 22, 2014 at 9:10 PM, Alban Hertroys haram...@gmail.com
wrote:
On 22 August 2014 14:26, Soni M diptat...@gmail.com wrote:
Currently we have only latest_transmission_id as FK, described here :
TABLE ticket CONSTRAINT fkcbe86b0c6ddac9e FOREIGN KEY
(latest_transmission_id) REFERENCES transmission_base(transmission_id)
Change the query to include only FK still result the same:
explain select t.ticket_id ,
tb.transmission_id
from ticket t,
transmission_base tb
where t.latest_transmission_id = tb.transmission_id
and tb.parse_date ('2014-07-31');
QUERY PLAN
--
Hash Join (cost=113928.06..2583606.96 rows=200338 width=8)
Hash Cond: (t.latest_transmission_id = tb.transmission_id)
- Seq Scan on ticket t (cost=0.00..1767767.26 rows=69990826
width=8)
- Hash (cost=108923.38..108923.38 rows=400374 width=4)
- Index Scan using transmission_base_by_parse_date on
transmission_base tb (cost=0.00..108923.38 rows=400374 width=4)
Index Cond: (parse_date '2014-07-31
00:00:00'::timestamp
without time zone)
(6 rows)
Do you have an index on ticket (latest_transmission_id)?
Yes, both t.latest_transmission_id and tb.transmission_id is indexed.
Indexes:
transmission_base_pkey PRIMARY KEY, btree (transmission_id) CLUSTER
Indexes:
ticket_by_latest_transmission btree (latest_transmission_id)
Okay, so we got those indexes. So much for the low-hanging fruit.
From the above plan we learn that the database estimates[1] that 400k rows
from transmission match your condition (parse_date '2014-07-31’). The
ticket table has a foreign key to that table, which suggests a 1:n
relationship. It also has around 70M rows, or at least the database seems
to think that about that amount will match those 400k transmissions.
That means that if on average 175 (=70M/400k) ticket ID’s match a
transmission ID, the database would be needing all those 70M rows anyway -
and even if it only needs every 175th row, a sequential scan is not a
particularly inefficient way to go about this.
The alternative is a whole lot of index lookups, probably not in the same
order as either the index or the rows on disk, meaning quite a bit of
random disk I/O.
I’m suspecting that the cost estimates for this query with seq-scans
disabled aren’t very different, provided doing so comes up with a
comparable plan.
Things you might want to verify/try:
* Are those estimated numbers of rows accurate? If not, is autovacuum (or
scheduled vacuum) keeping up with the amount of data churn on these tables?
Do you collect a sufficiently large sample for the statistics?
* How much bloat is in these tables/indexes?
* Did you change planner settings (such as disabling bitmap scans; I kind
of expected one here) or did you change cost estimate parameters?
* Does it help to put an index on transmission (parse_date,
transmission_id)?
* If none of that helps, we’re going to need the output of explain analyze
- that will probably take long to create, so you might as well start with
that and do the other stuff at the side.
What kind of hardware are these disks on? Is it possible that disk I/O on
this particular machine is relatively slow (relative to the seq/random cost
factor for disk access as specified in your postgresql.conf)?
Cheers,
Alban Hertroys
[1] You did not provide explain analyse output, so we only have estimates
to work with.
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
Regards,
Soni Maula Harriz
Re: [GENERAL] Query planner question
On Thu, Aug 21, 2014 at 9:26 AM, David G Johnston
david.g.johns...@gmail.com wrote:
Soni M wrote
Hi Everyone,
I have this query :
select t.ticket_id ,
tb.transmission_id
from ticket t,
transmission_base tb
where t.latest_transmission_id = tb.transmission_id
and t.ticket_number = tb.ticket_number
and tb.parse_date ('2014-07-31');
Execution plan: http://explain.depesz.com/s/YAak
Indexes on ticket :
ticket_pkey PRIMARY KEY, btree (ticket_id) CLUSTER
ticket_by_latest_transmission btree (latest_transmission_id)
ticket_by_ticket_number btree (ticket_number)
This query only returns some portions of rows from ticket table.
The question is, Why does postgres need to get all the rows from ticket
table in order to complete this query?
Can't postgres use indexes to get only needed rows on ticket table?
I try set seqscan to off, but still index scan try to get all rows on
ticket table.
Here's the execution plan : http://explain.depesz.com/s/abH2
Short answer: you haven't defined (latest_transmission_id, ticket_number)
as being a foreign key onto the transmission_base table yet you seem to
want
it to act like one.
Currently we have only latest_transmission_id as FK, described here :
TABLE ticket CONSTRAINT fkcbe86b0c6ddac9e FOREIGN KEY
(latest_transmission_id) REFERENCES transmission_base(transmission_id)
Change the query to include only FK still result the same:
explain select t.ticket_id ,
tb.transmission_id
from ticket t,
transmission_base tb
where t.latest_transmission_id = tb.transmission_id
and tb.parse_date ('2014-07-31');
QUERY PLAN
--
Hash Join (cost=113928.06..2583606.96 rows=200338 width=8)
Hash Cond: (t.latest_transmission_id = tb.transmission_id)
- Seq Scan on ticket t (cost=0.00..1767767.26 rows=69990826 width=8)
- Hash (cost=108923.38..108923.38 rows=400374 width=4)
- Index Scan using transmission_base_by_parse_date on
transmission_base tb (cost=0.00..108923.38 rows=400374 width=4)
Index Cond: (parse_date '2014-07-31 00:00:00'::timestamp
without time zone)
(6 rows)
I've googling this one, it seems that's how hash join works. For hash join
operation, the join predicate cannot be used for the index scan, only
independent predicate can be used in index scan.
http://use-the-index-luke.com/sql/join/hash-join-partial-objects
Because of this failure the planner considers the following:
Nested Looping over 380,000 records is going to suck so it tries some
advanced merge/join techniques to try and speed things up. In any such
alternative the entire ticket table needs to be considered since there is
no
constraint provided for that table - the only constraint in on
transmission_base and it rightly is using an index to find records matching
the where clause.
Since ticket_number and latest_transmission_id are found in separate
indexes
I do not believe the planner can make use of an Index Only scan to fulfill
the join so each index lookup would require a corresponding heap lookup
which means extra work compared to just sequentially scanning the heap in
the first place. Since it is going to hit the entire thing in either case
the sequential scan is the logical choice for it to make.
Others will correct any factual mistakes I may have made - I am theorizing
here and do not understand the planner sufficient well to be 100% certain
that an FK definition will solve the problem.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Query-planner-question-tp5815659p5815661.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Regards,
Soni Maula Harriz
Re: [GENERAL] Query planner question
On Fri, Aug 22, 2014 at 9:10 PM, Alban Hertroys haram...@gmail.com wrote:
On 22 August 2014 14:26, Soni M diptat...@gmail.com wrote:
Currently we have only latest_transmission_id as FK, described here :
TABLE ticket CONSTRAINT fkcbe86b0c6ddac9e FOREIGN KEY
(latest_transmission_id) REFERENCES transmission_base(transmission_id)
Change the query to include only FK still result the same:
explain select t.ticket_id ,
tb.transmission_id
from ticket t,
transmission_base tb
where t.latest_transmission_id = tb.transmission_id
and tb.parse_date ('2014-07-31');
QUERY PLAN
--
Hash Join (cost=113928.06..2583606.96 rows=200338 width=8)
Hash Cond: (t.latest_transmission_id = tb.transmission_id)
- Seq Scan on ticket t (cost=0.00..1767767.26 rows=69990826
width=8)
- Hash (cost=108923.38..108923.38 rows=400374 width=4)
- Index Scan using transmission_base_by_parse_date on
transmission_base tb (cost=0.00..108923.38 rows=400374 width=4)
Index Cond: (parse_date '2014-07-31 00:00:00'::timestamp
without time zone)
(6 rows)
Do you have an index on ticket (latest_transmission_id)?
Yes, both t.latest_transmission_id and tb.transmission_id is indexed.
Indexes:
transmission_base_pkey PRIMARY KEY, btree (transmission_id) CLUSTER
Indexes:
ticket_by_latest_transmission btree (latest_transmission_id)
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
--
Regards,
Soni Maula Harriz
[GENERAL] Query planner question
Hi Everyone,
I have this query :
select t.ticket_id ,
tb.transmission_id
from ticket t,
transmission_base tb
where t.latest_transmission_id = tb.transmission_id
and t.ticket_number = tb.ticket_number
and tb.parse_date ('2014-07-31');
Execution plan: http://explain.depesz.com/s/YAak
Indexes on ticket :
ticket_pkey PRIMARY KEY, btree (ticket_id) CLUSTER
ticket_by_latest_transmission btree (latest_transmission_id)
ticket_by_ticket_number btree (ticket_number)
This query only returns some portions of rows from ticket table.
The question is, Why does postgres need to get all the rows from ticket
table in order to complete this query?
Can't postgres use indexes to get only needed rows on ticket table?
I try set seqscan to off, but still index scan try to get all rows on
ticket table.
Here's the execution plan : http://explain.depesz.com/s/abH2
Thanks
--
Regards,
Soni Maula Harriz
Re: [GENERAL] Can I see the detailed log of query fired by particular Postgres client on Postgres server?
On each session created by the client, run set log_statement to 'all' before firing your query On Wed, Aug 13, 2014 at 4:21 PM, M Tarkeshwar Rao m.tarkeshwar@ericsson.com wrote: Hi all, Can I see the detailed log of query fired by particular Postgres client on Postgres server? This client might be any java application or any console Postgres client. I want this for our debugging purpose. Regards Tarkeshwar -- Regards, Soni Maula Harriz
Re: [GENERAL] Can I see the detailed log of query fired by particular Postgres client on Postgres server?
check you postgresql.conf, where you're heading your log, log_destination, etc. http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html On Wed, Aug 13, 2014 at 4:37 PM, M Tarkeshwar Rao m.tarkeshwar@ericsson.com wrote: Thanks for reply. We tried it but it is creating blank log file on pg_log. *From:* Soni M [mailto:diptat...@gmail.com] *Sent:* 13 August 2014 15:02 *To:* M Tarkeshwar Rao *Cc:* pgsql-general@postgresql.org *Subject:* Re: [GENERAL] Can I see the detailed log of query fired by particular Postgres client on Postgres server? On each session created by the client, run set log_statement to 'all' before firing your query On Wed, Aug 13, 2014 at 4:21 PM, M Tarkeshwar Rao m.tarkeshwar@ericsson.com wrote: Hi all, Can I see the detailed log of query fired by particular Postgres client on Postgres server? This client might be any java application or any console Postgres client. I want this for our debugging purpose. Regards Tarkeshwar -- Regards, Soni Maula Harriz -- Regards, Soni Maula Harriz
Re: [GENERAL] Postgresql 9.3 tuning advice
Genereal advice is to set up shared_buffers to 25% of total RAM. 75% RAM for OS cache. On my case (1.5 TB database, 145 GB RAM), setting shared_buffers bigger than 8GB would give no significant performance impact. On some cases, setting it low would be an advantage http://www.depesz.com/2007/12/05/shared-buffers-and-their-impact-on-performance/ On Tue, Aug 12, 2014 at 10:25 PM, dushy dushya...@gmail.com wrote: Hello all, Iam running a postgresql 9.0.13 master/slave instance in a write heavy workload. The hardware is a Dell 720, E5530 - 8 core, 128GB RAM. The database (around 250g with indexes/bloat etc) is sitting on flashcache device with 2 fusion- io PCIe MLC SSDs as frontend and a MD3200 based RAID10 (14*1TB SATA disks) as backend. OS is centos 6.2 with kernel 2.6.32-431.20.3.el6.x86_64. Currently, the performance related configuration is mostly default i,e shared_buffers, effective_cache_size. The only directive that seems different is checkpoint_segments = 96 Iam moving to postgresql 9.3 shortly and planning to tune the above directives as below.. effective_cache_size = 100GB # free+buffers is pretty consistent around 110 to 120GB and pg_oscache_total is around 80GB checkpoint_segments = 32 # 96 seems to long and all flushes seem to be only due to checkpoint_timeout with the older value Additionally iam turning off THB defrag on the OS as suggested by some posts on the lists. Though, My initial pgbench testing doesn't seem to indicate any issues with THB defrag turned on/off. Iam not sure about shared_buffers and wal_buffers for this HW/OS DB combination - iam inclined to leave them to defaults. But based on this article (http://rhaas.blogspot.in/2012/03/tuning-sharedbuffers-and-walbuffers.html ) it looks there will be some advantages in tuning it What would be a good value (to start with atleast) for shared_buffers and wal_buffers ? Please let me know if additional information will help. TIA dushy -- Regards, Soni Maula Harriz
[GENERAL] Re: [GENERAL] wired problem for a 9.1 slave：receive wal but do not replay it？
Do you run intensive read query on slave ? If yes, query conflict can cause that, http://www.postgresql.org/docs/9.1/static/hot-standby.html#HOT-STANDBY-CONFLICT On conflict, xlog stream will be saved on xlog dir on slave instead of replaying it. This happen until slave has opportunity to write all xlog into disk. On Mon, Aug 11, 2014 at 5:24 PM, Jov am...@amutu.com wrote: Today,our monitor report a pg slave instance'disk space usage reach 96%，I login in to the machine，and find the pg_xlog dir take up more than 2TB，which is abnormal. the number of WAL file in the pg_xlog dir is more than 130k,while we set the wal keep number to 8192. I think there is something stop the replay,so I check the pg_stat_activity,pg_prepare_statement,pg_xact etc,but find all normal. I run： ps auxwww | grep postgres and can find the wal receiver and streaming receiver work happily，because the wal file name，the streaming log id changed. So I have no idea. I then restart the slave PG,and find it recover from a very old wal which is one month ago. We are now set up a new slave for the master while let the recover from this slave go. the PG version is 9.1.9,OS is CentOS 6 x86-64. Jov blog: http:amutu.com/blog http://amutu.com/blog -- Regards, Soni Maula Harriz
[GENERAL] Streaming replica refuse to do restore_command
Hello All, This is how i set up the db : Slave using streaming replica. We configure slave to run pg_dump which usually last for about 12 hours. We have limited pg_xlog on slave. Once the pg_xlog on slave is full while pg_dump still in progress. 2014-08-11 09:39:23.226 CDT,,,25779,,53d26b30.64b3,2,,2014-07-25 09:35:28 CDT,,0,FATAL,53100,could not write to file pg_xlog/xlogtemp.25779: No space left on device, after some restart point, the slave is trying to connect to primary and refuse to do restore_command. Is this an expected behaviour? Full log : *2014-08-11 09:39:23.226 CDT,,,25779,,53d26b30.64b3,2,,2014-07-25 09:35:28 CDT,,0,FATAL,53100,could not write to file pg_xlog/xlogtemp.25779: No space left on device,* *2014-08-11 09:46:36.163 CDT,,,14394,,53c582ea.383a,14031,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint starting: time,* 2014-08-11 09:49:33.786 CDT,,,14394,,53c582ea.383a,14032,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint complete: wrote 21862 buffers (4.2%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=176.358 s, sync=1.243 s, total=177.623 s; sync files=683, longest=0.159 s, average=0.001 s, 2014-08-11 09:49:33.787 CDT,,,14394,,53c582ea.383a,14033,,2014-07-15 14:37:14 CDT,,0,LOG,0,recovery restart point at 2AC5/36CE52A0,last completed transaction was at log time 2014-08-10 23:01:55.754838-05 2014-08-11 09:49:33.790 CDT,,,14394,,53c582ea.383a,14034,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint starting: xlog, 2014-08-11 09:51:37.256 CDT,,,14394,,53c582ea.383a,14035,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint complete: wrote 76725 buffers (14.6%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=122.170 s, sync=1.284 s, total=123.465 s; sync files=770, longest=0.424 s, average=0.001 s, 2014-08-11 09:51:37.256 CDT,,,14394,,53c582ea.383a,14036,,2014-07-15 14:37:14 CDT,,0,LOG,0,recovery restart point at 2AC5/BE0D9590,last completed transaction was at log time 2014-08-11 01:10:49.220537-05 2014-08-11 09:51:37.258 CDT,,,14394,,53c582ea.383a,14037,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint starting: xlog, 2014-08-11 09:54:23.552 CDT,,,14394,,53c582ea.383a,14038,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint complete: wrote 49243 buffers (9.4%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=164.859 s, sync=1.428 s, total=166.293 s; sync files=794, longest=0.157 s, average=0.001 s, 2014-08-11 09:54:23.552 CDT,,,14394,,53c582ea.383a,14039,,2014-07-15 14:37:14 CDT,,0,LOG,0,recovery restart point at 2AC6/571657C8,last completed transaction was at log time 2014-08-11 04:43:16.23604-05 2014-08-11 09:54:23.561 CDT,,,14394,,53c582ea.383a,14040,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint starting: xlog, 2014-08-11 09:57:51.829 CDT,,,14394,,53c582ea.383a,14041,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint complete: wrote 215682 buffers (41.1%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=206.897 s, sync=1.364 s, total=208.267 s; sync files=697, longest=0.110 s, average=0.001 s, 2014-08-11 09:57:51.829 CDT,,,14394,,53c582ea.383a,14042,,2014-07-15 14:37:14 CDT,,0,LOG,0,recovery restart point at 2AC7/FBE0D48,last completed transaction was at log time 2014-08-11 06:42:22.051994-05 2014-08-11 09:57:51.832 CDT,,,14394,,53c582ea.383a,14043,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint starting: xlog, 2014-08-11 10:00:18.828 CDT,,,14394,,53c582ea.383a,14044,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint complete: wrote 127358 buffers (24.3%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=145.721 s, sync=1.263 s, total=146.995 s; sync files=905, longest=0.038 s, average=0.001 s, 2014-08-11 10:00:18.828 CDT,,,14394,,53c582ea.383a,14045,,2014-07-15 14:37:14 CDT,,0,LOG,0,recovery restart point at 2AC7/E80EDE30,last completed transaction was at log time 2014-08-11 07:15:48.30725-05 2014-08-11 10:00:18.833 CDT,,,14394,,53c582ea.383a,14046,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint starting: xlog, 2014-08-11 10:03:27.063 CDT,,,14394,,53c582ea.383a,14047,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint complete: wrote 168653 buffers (32.2%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=186.808 s, sync=1.409 s, total=188.229 s; sync files=886, longest=0.151 s, average=0.001 s, 2014-08-11 10:03:27.063 CDT,,,14394,,53c582ea.383a,14048,,2014-07-15 14:37:14 CDT,,0,LOG,0,recovery restart point at 2AC8/6D003160,last completed transaction was at log time 2014-08-11 07:40:58.390313-05 2014-08-11 10:03:27.065 CDT,,,14394,,53c582ea.383a,14049,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint starting: xlog, 2014-08-11 10:06:43.148 CDT,,,14394,,53c582ea.383a,14050,,2014-07-15 14:37:14 CDT,,0,LOG,0,restartpoint complete: wrote 220017 buffers (42.0%); 0 transaction log file(s)
Re: [GENERAL] Streaming replica refuse to do restore_command
On Tue, Aug 12, 2014 at 12:37 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Tue, Aug 12, 2014 at 2:10 PM, Soni M diptat...@gmail.com wrote: This is how i set up the db : Slave using streaming replica. We configure slave to run pg_dump which usually last for about 12 hours. We have limited pg_xlog on slave. Once the pg_xlog on slave is full while pg_dump still in progress. What is the version of Postgres you are using? Pg 9.1 on from RHEL packages after some restart point, the slave is trying to connect to primary and refuse to do restore_command. Is this an expected behaviour? 2014-08-11 10:15:53.298 CDT,,,29141,,53e8de29.71d5,2,,2014-08-11 10:15:53 CDT,,0,FATAL,XX000,could not receive data from WAL stream: FATAL: requested WAL segment 00032ACC00A6 has already been removed , 2014-08-11 10:15:58.550 CDT,,,29146,,53e8de2e.71da,1,,2014-08-11 10:15:58 CDT,,0,LOG,0,streaming replication successfully connected to primary, A restart point is created each time a standby server shuts down during WAL replay. It seems that you stopped it for a too long time, and that WAL files that this server expected to find are done because it was out of sight for a too long time. Then, when a standby starts and enters in recovery, it tries first to recover necessary WAL files from the archives with restore_command and then switches to streaming mode if it cannot find what is necessary. Are you sure that you kept enough WAL history in your archives and that restore_command is pointing to the right path/host? Regards, -- Michael the wal files needed still on the archives and it's on the right path. I try this and it's solved. I moved all pg_xlog files to a bigger storage and manually copy needed wal files from archive, and restart the server and then it runs smoothly -- Regards, Soni Maula Harriz
Re: [GENERAL] Taking rsynced base-backup without wal-archiving enabled
i think you could try pg_basebackup tools. it has options to achieve same thing as you wanted. but need pgdata on destination emptied. if you really need to do the exact thing as you stated, then you need to set postgres to keep high enough number of xlog files on master to ensure that needed xlog files not removed prior to completed backup. but no guarantee, since the database activity is not a static one. On Mon, Aug 4, 2014 at 11:15 PM, Bruce Momjian br...@momjian.us wrote: On Mon, Jun 23, 2014 at 09:28:06PM -0500, Rene Romero Benavides wrote: It depends on the database transactional activity, observe how many new wal files are generated during a period equivalent to what it takes to do your base backup. I would set it to twice that number. Take into account that a checkpoint is issued at the beginning of the process. If you're lazy just try setting it to something very high such as 256 or more to prevent wal files being recycled during the process. 2014-06-23 2:12 GMT-05:00 J rgen Fuchsberger juergen.fuchsber...@gmx.at : Hi all, Can I do a consistent file-system-level backup using the following procedure: 1) SELECT pg_start_backup(...) 2) rsync postgres data dir to another server 3) SELECT pg_stop_backup() 4) rsync pg_xlog directory From what I understand this should be similar to running pg_basebackup using the -x parameter, correct? One caveat seems to be that wal_keep_segments should be set high enough. Can anybody tell what high enough usually is? I am coming late to this thread, but it seems easier for the user to set archive_command to something meaningful during start/stop backup, and set it to /bin/true at other times. I realize they can't turn archive_mode on/off without a restart. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Regards, Soni Maula Harriz
