Re: [ADMIN] pg_stop_backup is not archiving latest transaction log from pg_xlog directory
On 19 June 2013 09:32, girish R G peetle wrote: > Is there a way to switch the current transaction log file in Postgres 8.0 ? No, that was an addition to 8.2 Suggest that you send more data until the WAL file switches. You should upgrade with some urgency. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Transaction ID wrap limit is log entries
On 19 May 2013 13:54, Armand du Plessis wrote: > We started seeing 1000s of messages like the ones below in our logs starting > last night. There's been no changes but performance has dropped > significantly. > Any ideas what would be causing this? The database has not been issuing VACUUMs on some or all of the relations, so the system needs to issue "anti-transactional wraparound" VACUUMs. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Hot-standby and canceled queries
On 24 April 2013 12:42, David F. Skoll wrote: > I ran a query on a hot-standby (PostgreSQL 9.1) and it was canceled as > per the documentation about vacuum cleanups on the master. > > Second time testing, I started a transaction on the master (with > BEGIN) but my query on the hot-standby was still canceled. > > Is that because a transaction doesn't "really" begin with BEGIN, but only > after the first real statement after the BEGIN? Is it sufficient to > use: BEGIN; SELECT ... ; to start a transaction on the master Yes >, or > do I need to use UPDATE or INSERT to ensure that a vacuum cleanup isn't > applied until the transaction is finished? SELECT is sufficient -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] MySQL Blackhole Engine
On 6 November 2012 23:25, Tom Lane wrote: > Deron writes: >> We are looking at integrating a 3rd party application. They currently use >> MySQL for their database solution. We are asking them to see about porting >> to PostgreSQL. One of the issues they are running into is that they use >> MySQL "BLACKHOLE" storage engine. > > Really? I've always assumed that was utterly useless except as a > template for writing new mysql storage engines. What are they using it > for? I had also assumed that they were useless. The docs say that the actions are binary logged, but the actual INSERT ignored. Which is exactly what Hannu is asking for with LOG ONLY TABLEs. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Massive insert created 975 WAL segments.. what?
On 12 September 2012 01:10, Wells Oliver wrote: > We were doing a test and inserted 1.5 million rows. In doing so, postgres > created 974 WAL segments of 16MB apiece. The relevant configuration from my > master's postgresql.conf: > > wal_level = hot_standby > archive_mode = on > archive_command = 'test ! -f /mnt/postgresql-logs/%f && cp %p > /mnt/postgresql-logs/%f' > max_wal_senders = 3 > wal_keep_segments = 16 > > So, now I have 16GB of WAL segments, 974 of them. Will postgresql clean this > up? Will it remove these files? Will it create more with big inserts? > > What is the best way to manage this? These are transaction log files, created by database writes. They protect you if you crash and also allow replication/backup. These will be rotated every checkpoint, which by default is 5 minutes. So they'll be long gone by now. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Data corruption after SAN snapshot
On 7 August 2012 23:23, Terry Schmitt wrote: > I have a pretty strange issue that I'm looking for ideas on. > I'm using Postgres Plus Advanced Server 9.1, but I believe this problem is > relevant to Postgres Community. It is certainly possible to be a EDB bug and > I am already working with them on this. I think its important to say that as soon as someone patches/forks Postgres code then the onus is really on whoever did that to show the problem wasn't created by them when they did that. That is very much the reason why my company and many others don't market a forked product and stick closely to the community distributed binaries. I have much respect for your vendors ability to diagnose faults and recreate them on stock Postgres if appropriate, so I think we should wait for that to happen rather than attempt to answer this just yet. Or alternatively, please submit a test case that fails on Postgres if you doubt that. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_basebackup blocking all queries with horrible performance
On 13 June 2012 02:39, Magnus Hagander wrote: >> I'm running the following, which gets piped over ssh to a remote >> server (at gigabit ethernet speed): >> pg_basebackup -v -D - -x -Ft -U postgres >> >> One thing that I've discovered is that if I throttle back the speed of >> what is getting piped to the remote server, that directly correlates >> to the load on the server. > > That seems to indicate that you're overloading the I/O system... Or > the CPU, but more likely I/O. CPU utilisation of ssl connections is bad. If network bandwidth is good, perhaps running WALSender at full speed with encryption can tank the server. An effect related to cacheing of WAL files? Perhaps we need to mark them as FADV_DONTNEED at some point. Hard to say without detailed analysis. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] replication failure with GIN index
On Fri, Apr 6, 2012 at 2:56 AM, Rural Hunter wrote: > I'm trying to set up a standby server. Both the primary and standby servers > are on latest version 9.1.3 on ubunt server 10.10. So far I tried to init > the setup 2 times but both failed after the replication running for some > time. what can I do to fix this? The log on the standby is shown below: > > 2012-04-06 02:31:01 CST [@] LOG: restored log file > "00020E3C00F1" from archive > 2012-04-06 02:35:35 CST [@] LOG: restored log file > "00020E3C00F2" from archive > 2012-04-06 02:36:19 CST [@] LOG: restored log file > "00020E3C00F3" from archive > 2012-04-06 02:36:48 CST [@] LOG: restored log file > "00020E3C00F4" from archive > 2012-04-06 02:37:24 CST [@] LOG: restored log file > "00020E3C00F5" from archive > 2012-04-06 02:37:27 CST [@] PANIC: GIN metapage disappeared > 2012-04-06 02:37:27 CST [@] CONTEXT: xlog redo Update metapage, node: > 37547844/16405/83896882 blkno: 4294967295 > 2012-04-06 02:37:28 CST [@] LOG: startup process (PID 24912) was terminated > by signal 6: Aborted > 2012-04-06 02:37:28 CST [@] LOG: terminating any other active server > processes The blkno is all wrong, so it looks like a clear bug to me. Blkno has been set to -1. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Hot standby having high requested checkpoints?
On Tue, Feb 28, 2012 at 10:53 PM, Brian Fehrle wrote: > I have two PostgreSQL 9.0.5 clusters, one is a master and the other is a hot > standby via streaming replication. I'm monitoring some stats on each and I'm > noticing something very odd. On the master, I get between 2 and 4 requested > checkpoints per hour, but on the hot standby I'm seeing between 200 and 300 > requested checkpoints per hour. We don't do a restartpoint on the standby unless we see a checkpoint record, so that result should be impossible. So I'm guessing you're reading the stats wrong? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] two questions regarding warm-standby on 8.4
On Mon, Jan 23, 2012 at 8:05 PM, Gary Webster wrote: > Thanks for the informative replies. > > So, is Hot Standby the recommended way to do HA? > My application does not need to be able to do queries on the standby. Streaming replication is the recommended way to do HA, which is roughly "yes". -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] two questions regarding warm-standby on 8.4
On Mon, Jan 23, 2012 at 2:48 PM, Gary Webster wrote: > I am configuring warm-standby as documented here: > http://www.postgresql.org/docs/8.4/interactive/warm-standby.html > > I am using "pg_standby", & may have it working corrrectly, but am not sure. > > 1) The standby database never 'starts', right? (/etc/init.d/postgresql-8.4 > start" times out; I can't connect to the db to see if tables are correct). > > 2) Because of #1, I suppose I can't do a pg_dump on the standby db? Exactly. That's what Hot Standby does in 9.x -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] truncate table in pitr system
2011/12/7 Silvio Brandani : > I have a system with archive log shipping to a standby server read only with > replication streaming. > is possible to use the truncate in the primary or it will affect the log > shipping architecture and will corrupt the standby server?? It Just Works. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Point in time recovery
On Wed, Nov 23, 2011 at 12:39 PM, Karuna Karpe wrote: > Can any one please explain me how to use PITR? Yes, the docs contain a great explanation. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] after failover?
On Mon, Oct 17, 2011 at 2:44 PM, Ray Stell wrote: > On Sun, Oct 16, 2011 at 08:21:31PM +0100, Simon Riggs wrote: >> On Fri, Oct 14, 2011 at 3:02 PM, Ray Stell wrote: >> >> > The tutorial here excludes an important issue: >> > http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#Failing_Over_To_The_Standby >> > After the failover is complete, it seems like a restart is required to >> > make the new primary capable of supporting a new hot standby. ?Can the >> > config values be changed without an interuption of service? ?I'm using >> > a hotstandby config in 9.0.x. ?Anything different in this regard in 9.1? >> > Thanks. >> >> There's no reason not to have wal_level = hot_standby on the standby also. > > That's helpful, thanks. Seems like a worthy addition to the doc and > tutorial. Doc patches welcome. Wiki changes can be made directly. Thanks. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] after failover?
On Fri, Oct 14, 2011 at 3:02 PM, Ray Stell wrote: > The tutorial here excludes an important issue: > http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#Failing_Over_To_The_Standby > After the failover is complete, it seems like a restart is required to > make the new primary capable of supporting a new hot standby. Can the > config values be changed without an interuption of service? I'm using > a hotstandby config in 9.0.x. Anything different in this regard in 9.1? > Thanks. There's no reason not to have wal_level = hot_standby on the standby also. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] replication from Oracle to PostgreSQL?
On Thu, Aug 11, 2011 at 3:41 PM, CS DBA wrote: > Anyone know of tools / options that will allow Oracle to PostgreSQL > replication? or at least a real time feed or dblink? 2ndQuadrant has various in-house tools for fast migration from Oracle to PostgreSQL, but those aren't designed for long term co-existence, just for minimising the window of downtime at cutover. It's not been weaponised, so its not available for free download. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Frequency of archive_cleanup_command
On Wed, Aug 10, 2011 at 6:36 PM, A J wrote: > Couple of questions: > 1. At what frequency is the archive_cleanup_command executed from the > recovery.conf file on the standby ? > 2. If for some reason lets say a WAL file is missing from the archive > directory but the files after that are present. When the standby tries to > restore the missing WAL file, does it fail or it simply skips the missing > WAL file and goes to the next one. It fails. The gap must be repaired manually, or you're out of luck. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] vacuumdb question/problem
On Fri, Jul 15, 2011 at 5:10 PM, David Ondrejik wrote: > Since then, the process has continued to run (for about 20 hrs) without any > additional information being returned. Probably locked behind another long running task that is holding a buffer pin. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] How to start a database in ReadOnly mode?
On Thu, Jul 7, 2011 at 8:08 AM, Paul Schluck wrote: > Is it possible to start a postgres cluster or 1 postgres database in > readonly mode? And - if yes - how can this be done? There is no formal, explicit command to do that. Just avoid doing writes, either in the application or by removing privileges. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PANIC while doing failover (streaming replication)
On Fri, Jul 1, 2011 at 6:54 AM, Fujii Masao wrote: > http://archives.postgresql.org/pgsql-hackers/2008-12/msg01335.php > According to the above thread, this issue seems to exist for a few years, > and be unsolved yet. Could you provide a self-contained test case? I'll add some more logging to help diagnose this. Those log reports are mostly useless in the form they are in now. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Prepared statments: partial indexes are avoided!
On Mon, Jun 20, 2011 at 3:01 PM, adrien ducos wrote: > I have postgres 8.4 > > I have a table "foo" with 16 million lines 99% of those lines have a column > "bar" = 1. > > I had an index on this table: > CREATE INDEX index_foo_bar ON foo using btree (bar); > > The thing is that the query > select count(*) from foo where bar = 1; > is not using the query (it is useless that is normal, using this index would > be slower than not using it) > > the query > select count(*) from foo where bar = 2; uses the index I have the answer in > 20ms. > > With a prepared statement I have > > PREPARE fooplan (int) AS > select count(*) from foo where bar = $1; > execute fooplan (2); > > also a few milliseconds > > Now in order to optimise this last request I droped the first index and > added a new index: > > CREATE INDEX index_foo_bar ON foo using btree (bar) where > created_by_user_group <> 1; > > since the query is only using the index in this case anyway. > > with the query > explain analyze select count(*) from foo where bar = 2; it uses the new > index : > > "Aggregate (cost=8.29..8.30 rows=1 width=0) (actual time=0.119..0.119 > rows=1 loops=1)" > " -> Index Scan using index_foo_bar on foo (cost=0.00..8.29 rows=1 > width=0) (actual time=0.017..0.084 rows=63 loops=1)" > " Index Cond: (bar = 2)" > "Total runtime: 0.144 ms" > > so great improvement from 20ms to 0.144ms > > and with the prepared statement... things becomes very bad: > PREPARE fooplan (int) AS > select count(*) from foo where bar = $1; > explain analyze execute fooplan (2); > > > "Aggregate (cost=627406.08..627406.09 rows=1 width=0) (actual > time=11627.315..11627.316 rows=1 loops=1)" > " -> Seq Scan on foo (cost=0.00..603856.80 rows=9419712 width=0) (actual > time=7070.334..11627.266 rows=63 loops=1)" > " Filter: (bar = $1)" > "Total runtime: 11627.357 ms" > > No index uses and the time becomes very bad. > > This is probably due to the fact the prepared statement could have "1" as an > input and so it avoids the index completely, but this is not very nice for > software optimization since I am using pdo which uses prepared statements > all the time and is unable to use all the partial indexes. > > The problem is I have 90 GB of indexes in the database and partial indexes > could help me to save some of this space on my server, in addition to > improve the speed of the queries. Unfortunately, prepared statements do act in the way you have seen. I have a patch into 9.2 under discussion to improve upon this situation, but don't hold your breath for that. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Re: could not open temporary statistics file "pg_stat_tmp/pgstat.tmp"
On Thu, Jun 16, 2011 at 7:44 AM, bakkiya wrote: > Space,permission are not an issue. Then presumably you have your stats_temp_directory parameter set to a path where that is not true. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] could not open temporary statistics file "pg_stat_tmp/pgstat.tmp"
On Thu, Jun 16, 2011 at 6:28 AM, bakkiya wrote: > We have postgresql9.0 in our Linux server. We are continously getting "could > not open temporary statistics file "pg_stat_tmp/pgstat.tmp": No such file or > directory" message in the db log file.We have data/pg_stat_tmp directory in, > but pgstat.tmp file is not ppresent in this directory.Can someone tell us > why this error messgae is logged? The file is being opened for write. Permissions? Space? Kernel settings? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Reindexing primary keys without locking
On Mon, Jun 13, 2011 at 6:47 AM, Stuart Bishop wrote: > On Wed, Jun 1, 2011 at 7:38 PM, Glyn Astill wrote: >> Hi Guys, >> Does anyone know of any tricks to replace primary key indexes without using >> reindex? Or any other method that will not lock the table for an extended >> amount of time on 8.4? >> I've got a bloated primary key index on a table and I'd like to try and >> clean it up without downtime. > > If you are brave, > http://pqxx.org/development/libpqxx/wiki/PrimaryKeyTransplant > > If you go this route, ensure you have a test environment and ensure > you can dump and restore your db schema after applying the patch, and > that the diff of the before and after schemas checks out. If you screw > up, you can end up with crazy things like multiple indexes with the > same name and other stuff making your database unrecoverable. Stuart, That looks interesting. There's a similar process in the PostgreSQL Admin Cookbook, though our work was independent. I guess that's because in 9.0 and below it really is the only way to make it work correctly. Will look for your work in future, thanks. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Sync replication waits for only 1 slave
On Tue, Jun 7, 2011 at 10:33 PM, A J wrote: > Having a master and 2 slaves in synchronous replication mode in 9.1 > The master seems to wait for only one slave to respond before considering > the transaction to be complete. I have done the setting for master to wait > for all the slaves to finish: > synchronous_standby_names = '*' > I even tried replacing * with both the slave application_names. > Still master just waits for a single slave. Am I doing something incorrect > or is it a limitation ? No, its a design decision to improve availability and performance. You can list the second standby also, so it can take over in the event of failure. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Switchover of Master and Slave roles
On Tue, Jun 7, 2011 at 8:41 PM, Ray Stell wrote: > On Tue, Jun 07, 2011 at 11:59:07AM -0700, A J wrote: >> We then rely on filesystem tools (or other third party tools) to get the >> original master in sync with the new master efficiently and then make it >> join as >> slave. > > the doc would not be corrupted by an additional few words that stated as such: > > "To return to normal operation, a standby server must be recreated using > external tools such as rsync, either on the former primary system when > it comes up, or on a third, possibly new, system." You need repmgr: http://www.repmgr.org/ -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Insert statement deletes older table records
On Mon, May 30, 2011 at 1:07 AM, Rabi Jay wrote: > When I execute an insert statement, older table records are deleted even > though my insert statement works. It always keeps the maximum number of > records in the table to 4200 records. So If I added 3 more records, it > deletes the first three records in the table even though it adds the 3 new > records. > > Based on my research it appears postgreql has no limit to number of records > in a table - what should I do to avoid this behavior. If this is a real report, please come back with further details of the actions you are performing, version numbers and related details. If what you say is correct then this is a data loss bug and this project takes such things very seriously. Having said that, this is the second time in last few months that some "hearsay" has emerged relating to there being a limit on the number of rows in a PostgreSQL database. So I'm a little skeptical as to this as a credible bug report. PostgreSQL has never had a bug filed of this nature. The described behaviour would certainly have been picked up in testing or production usage if it was a bug, especially since tables with 1 billion+ rows in are fairly common. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_archivecleanup with multiple slaves
On Fri, May 20, 2011 at 4:58 PM, Tim Lewis wrote: > I don't actually use streaming replication, but what exactly is the problem > with the hard link for each slave solution, and the slaves handling there > own pg_archivecleanup? Doing so creates a single point of failure. We had problems with links in earlier versions, so we learned the hard way to stay clear of them. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Can we Flush the Postgres Shared Memory ?
On Tue, May 3, 2011 at 2:30 PM, raghu ram wrote: >>> The CHECKPOINT command will do this for you. >> >> > > According to PostgreSQL documentation, whenever you execute "CHECKPOINT" in > the database,it will flush the modified data files presented in the Shared > Buffers retuned to the Disk. > http://www.postgresql.org/docs/9.0/static/sql-checkpoint.html > Is this clears the entire shared memory cache and same time,if i execute > fresh SQL statement, Data will be retuned from disk?? No, but then you'd need to flush OS buffers and all disk caches as well to make that effective. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Can we Flush the Postgres Shared Memory ?
On Tue, May 3, 2011 at 3:16 PM, Mark Johnson wrote: > The contents of this email may not be copied or forwarded in part or in > whole without the express written consent of the author. Pleased to meet you Mark. If you post here, the above disclaimer is not effective. Right now your words are being copied across the internet... -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Can we Flush the Postgres Shared Memory ?
On Tue, May 3, 2011 at 11:54 AM, raghu ram wrote: > It may be a silly question, still out of curiosity I want to know, is there > any possible way to flush the Postgres Shared Memory without restarting the > cluster. > In Oracle, we can flush the SGA, can we get the same feature here.. > Thanks in Advance. The CHECKPOINT command will do this for you. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] DELETE FROM pg_description WHERE ...
On Fri, Apr 22, 2011 at 5:26 PM, Erwin Brandstetter wrote: > Hi! > > It is generally not advisable to write to system catalogs directly ... > > However, I have a database with dozens of schemas and hundreds of > tables. There is a bunch of useless comments on columns I want to get > rid of, scattered all across the db. The fastest & easiest way would > be: > > DELETE FROM pg_description WHERE description = 'My very special > totally useless comment.'; Why would you *not* use the COMMENT command for that? What is slower or harder about using it? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] database system identifier differs between the primary and standby
On Wed, Apr 20, 2011 at 2:13 PM, rajibdk wrote: > We are getting the following log while configuring hot standby, > > 2011-04-20 17:34:40 ETC/GMT FATAL: the database system is starting up > 2011-04-20 17:34:41 ETC/GMT FATAL: database system identifier differs > between the primary and standby > 2011-04-20 17:34:41 ETC/GMT DETAIL: The primary's identifier is > 5592072752411433371, the standby's identifier is 5597615802844953578. Please refer to http://www.postgresql.org/docs/9.0/static/warm-standby.html#STANDBY-SERVER-SETUP http://www.postgresql.org/docs/9.0/static/continuous-archiving.html#BACKUP-PITR-RECOVERY You might also want to look at the recent Postgres book that has a detailed chapter on Replication: http://www.2ndquadrant.com/books/postgresql-9-admin-cookbook/ -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] streaming AND file-based log-shipping?
On Fri, Apr 15, 2011 at 2:25 AM, Ray Stell wrote: > The cookbook text says, log shipping "is mostly superseded by streaming > replication in 9.0, though is still useful as part of a comprehensive > backup strategy." The implication is that the WAL archive might be > needed for recovery in some cases: Useful, not needed. > 1. Under what circumstances might the WAL archive come into play in recovery? If a WAL file is damaged, or if you wish to keep archived files for longer than they are strictly required to make replication work. > 2. If the streaming rep fails for some reason will the standby begin > recovery from the WAL archive, assuming the WAL can still be shipped? Yes > 3. What query or command set can be used to tell if file-based recovery is > configured correctly if streaming replication is active? > > I've configured both (I think), but it isn't clear to me whether > the file-based part will function. I guess I could bust the stream somehow > and see what happens, but I can't find this idea documented. Agreed, its slightly harder to test that its all working. But any action you take that only works if something broke, can only really be tested by deliberately breaking something. Break it, but on a test system. -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Warm Standby on 8.4.7
On Thu, Apr 14, 2011 at 1:06 PM, Alanoly Andrews wrote: > Thanks, Simon, for your quick response. I'll try it out with your pg_standby > utility. And...if I may ask you one more question on the subject, once the > standby is up and running, is there a way to determine whether it is in synch > with the primary database (in terms of the last WAL applied)? I guess that > the standby cannot be queried as it would be in permanent recovery mode. At 8.4, no, you cannot query the standby. 9.0 introduces the Hot Standby feature to allow you to query the standby. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Warm Standby on 8.4.7
On Wed, Apr 13, 2011 at 8:53 PM, Alanoly Andrews wrote: > Is it possible to set up a warm standby pair of postgres instances without > using the pg_standby utility? The PG manuals appear to say it is possible. > But I don’t see the details of how to set this up. How do you keep the > standby instance in permanent recovery mode? It is possible. You just need to write a utility very similar to pg_standby. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Streaming Replication limitations
2011/4/13 Tom Lane : > Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= writes: >> On Wed, 2011-04-13 at 23:23 +0530, raghu ram wrote: >>> Is there any limitations to configure streaming replication between >>> different operating systems i.e solaris 64 bit to RHEL 64 bit. > >> It won't work. > > As long as it's the same machine architecture, it probably will ... > but if "solaris" here really means "sparc" then I agree. > > Short answer is to test the case you have in mind and see. That's the long answer, not least because the absence of a failure in a test is not conclusive proof that it won't fail at some point in the future while in production. The short answer is "don't do it". -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Gap in sequence after fail-over
On Tue, Apr 12, 2011 at 9:28 AM, Simon Luijk wrote: > I have two test systems with Postgresql in Hot-standby configuration. The > intention > is to fail-over to the slave every so often. When ever we switch servers > (fail-over) > and start inserting data, the primary ids have a gap of ~30. From the > following > thread form 2001 it seems this maybe expected? > > http://archives.postgresql.org/pgsql-bugs/2001-12/msg00082.php > > With a Hot-standby set-up like this, that regularly switches, can I not rely > on > the sequence always incrementing by 1? No, you can't. Sequences cache their values for performance. Any crash or failover will produce the same effect. We might change that for switchovers but it would take some effort and would still be the same if it crashes. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] log entries while bringing a streaming replication standby server to life
On Fri, Apr 1, 2011 at 1:04 PM, Gerhard Hintermayer wrote: > I managed to set up a streaming replication with 9.0.3. This works like a > charm. > When I pull the trigger - I mean create the trigger file ;-) - I find > the following entries in the server log, is this normal or am I doing > something nasty ? > > [ @]LOG: streaming replication successfully connected to primary > (this is the last message from startup) > > [ @]LOG: trigger file found: /var/lib/postgresql/9.0/data/trigger > [ @]FATAL: terminating walreceiver process due to administrator command > [ @]LOG: redo done at 1/DC00D53C > [ @]LOG: last completed transaction was at log time 2011-04-01 > 13:46:35.679163+02 > rsync: link_stat "/0001000100DC" (in postgresql-wals) > failed: No such file or directory (2) > rsync error: some files/attrs were not transferred (see previous > errors) (code 23) at main.c(1518) [Receiver=3.0.8] > rsync: link_stat "/0002.history" (in postgresql-wals) failed: No > such file or directory (2) > rsync error: some files/attrs were not transferred (see previous > errors) (code 23) at main.c(1518) [Receiver=3.0.8] > [ @]LOG: selected new timeline ID: 2 > rsync: link_stat "/0001.history" (in postgresql-wals) failed: No > such file or directory (2) > rsync error: some files/attrs were not transferred (see previous > errors) (code 23) at main.c(1518) [Receiver=3.0.8] > [ @]LOG: archive recovery complete > [ @]LOG: autovacuum launcher started > [ @]LOG: database system is ready to accept connections > > I think the error message for the WAL is normal, as the file name is > increased until no WAL files to replay are found, and this log entries > I also get, when I bring up the standby from a base backup. > But what about the *.history entries ? Are they something to worry about ? > > BTW, my recovery.conf is: > > 'rsync -pog aprol1::postgresql-wals/%f %p' > trigger_file = '/var/lib/postgresql/9.0/data/trigger' > standby_mode = 'on' > primary_conninfo = 'host=10.5.17.40 port=5432 user=postgres' > > thanks for any input. That all looks normal to me, as far as I can tell. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] ERROR: could not access status of transaction
On Thu, Mar 24, 2011 at 1:43 PM, raghu ram wrote: > > > On Thu, Mar 24, 2011 at 6:09 PM, Stevie wrote: >> >> Hello pgsql-admins, >> >> we have a problem with our Postgresql 9.0.3 backup database (Ubuntu 10.4). >> The backup and restore is done as described here: >> http://www.postgresql.org/docs/9.0/static/continuous-archiving.html >> If you want to know the exact steps of backup and restore,please ask. >> >> We restore the database on the backup host and it's starting. I've append >> the mainlog. >> >> If we dump or reindex the database we get "could not access status of >> transaction NN" errors. >> PG tries to access old clog files: "pg_clog/00CD". But in the pg_clog >> directory the files start at "00E6" and end at "0198". >> I've append the pg_controldata output. This clog files doesn't exist on >> the original database neither. >> >> I could create empty clog files, but it seems that our database is in an >> inconsistent state? >> Any suggestions are welcome. >> > > There is a commit log corruption in your database. You need to make the > files with right size (256K of zeroes).A suitable "dd" from /dev/zero will > accomplish this on modernUnixen (ie, anything that has /dev/zero). Filling > of those files with zeroes nothing but " all those old transactions are > aborted". > > The safest way to resolve the issue by using the below command:- > > dd if=/dev/zero of=Transaction_missing_file_of pg_clog bs=256K count=1 (To > make the uncommitted record as they haven't been committed). > Just for the record, playing with pg_clog files like this is an insanely unsafe thing to do. Notably it will make all aborted transactions in that range become committed, leading to all sorts of fun. This is a desperate measures only action and not one that looks immediately related to the problem. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] replication solution
On Tue, 2010-08-24 at 15:46 +0200, Silvio Brandani wrote: > Hi all, > > we are looking for a solution to create a replicated database to be used > as reporting database with same data of production ( we can accept a lag > of some minutes). > > we already have the Point In Time Recovery but we need a solution where > the standby is always open in readonly , but the data is replicated > continuosly from primary. > > The Slony solution could be a possibility but the production database is > 80 Gb of data with around 1 transaction each hour. > > DO you have some suggestion for our implementaiont if exists?? PostgreSQL 9.0 is production ready now and supports Hot Standby, which is exactly what you want. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Need recommendation for PostgreSQL core developer/PostgreSQL admin
On Thu, 2010-06-17 at 17:27 -0400, Vineet Jain wrote: > Can someone recommend a PostgreSQL core developer/or experienced admin > who does some on demand consulting. I would like to purchase a 10-20 > hours of support and get support with > implementation/questions/best practices on replication/table > space layout/performance tunning etc. 2ndQuadrant provides all of these services. Be happy to hear from you. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] could not truncate directory "pg_subtrans": apparent wraparound
On Wed, 2010-05-26 at 09:01 +0300, Mikko Partio wrote: > With this database instance (beta1 initdb'd) I have not made > failovers. I don't think I have any special hot standby parameters > either. OK, so that pretty much rules out HS as a direct cause. > Non-default hot standby related configuration options (master > database): > > > wal_level = hot_standby # minimal, archive, or hot_standby > archive_mode = on # allows archiving to be done > archive_command = '/postgresql/bin/archive_wal.sh "%p" "%f"' ># command to use to archive a logfile segment > archive_timeout = 3600 # force a logfile segment switch after > this > hot_standby = off # allows queries during recovery > max_wal_senders = 3 # max number of walsender processes > wal_keep_segments = 10 # in logfile segments, 16MB each; 0 > disables -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] could not truncate directory "pg_subtrans": apparent wraparound
On Mon, 2010-05-24 at 08:46 +0300, Mikko Partio wrote: > It was freshly initdb'd with beta1 binaries, the contents were loded > from a pg_dump file. The number of transactions is very small, we're > talking about thousands (not billions). This database is the master of > a hot standby installation, if that matters. Have you ever performed a switchover operation? If you've never run an extended recovery on that server, its less likely to be anything HS related. Are you running any special hot standby parameters? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] List traffic
Traffic on the PostgreSQL lists is very high now and I freely admit that reading every email is simply not possible for me, even the ones that mention topics that keyword searches tell me are of potential interest. If anybody knows of a bug or suspected bug in my code, I have no problem in being copied in on mails so that I can see the issues exist. I do not promise to respond to every mail I'm copied on, though, but it at least helps me manage the fire hydrant. Thanks! -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PITR load on servers - increased 20%
On Tue, 2010-03-16 at 10:05 +, Renato Oliveira wrote: > I have been testing PITR and I have noticed a 20% increase on the load of the > Disk subsystem. > > Is that a normal thing to expect? Have you come across this or have you > noticed this increase? > > I know some of you guys have been using it for quite some time, would you > mind in sharing your experiences, please? I think you need to explain what things you've been testing and how. There are some well documented bulk data load optimizations that we cannot take advantage of when running archiving, but apart from those the load is actually very low in normal running. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Autovac vs manual with analyze
On Mon, 2010-03-15 at 13:20 -0500, Scott Whitney wrote: > My problem is that each Saturday at midnight, I have to start a > vacuumdb -f -z -a or my pg_clog dir never clears out. Why is your pg_clog dir a problem? > So, my questions are: > > a) Is the manual vacuum needed for performance reasons, or is auto-vac > sufficient? If you don't know why you are running it, then you should skip it and measure the difference. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_stop_backup()
On Wed, 2010-03-17 at 10:41 -0400, Vitaly Burshteyn wrote: > Out of curiosity, any idea why every time I run select > pg_stop_backup(); it goes into an infinite loop? When you say "infinite" do you mean to say it never comes out of the loop at all? Not sure if you are reporting a bug, or not. The manual describes what is happening: http://developer.postgresql.org/pgdocs/postgres/continuous-archiving.html#BACKUP-BASE-BACKUP Step 5 Is there anything you think should be added to the manual? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Off-site storage for PITR logs
On Mon, 2010-03-15 at 11:27 +0200, Nicos Panayides wrote: > thanks for the suggestion. What kind of space savings should I expected > from turning off full_page_writes? Substantial, though you should measure it and see, since it is workload dependent. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Off-site storage for PITR logs
On Fri, 2010-03-12 at 18:40 +0200, Nicos Panayides wrote: > I am planning an off-site backup solution for a fairly busy > (mostly-write) 8.3 database. The database is currently about 200GB in > size. I though about using log shipping and cold standby since it's easy > in terms of administration and also offers point in time recovery. > > The database generates about 3 PITR log files per minute. If my > calculations are correct the sites need to be connected with a 7MBit > connection and the logs will need about 68GB of storage per day! > > Does anyone have any suggestions on how to significantly reduce the > volume of log files or recommend another off-site backup solution that > would require less bandwidth and storage? Consider turning off full_page_writes which will permanently minimise the amount of WAL traffic, as well as being an integrated and fully-supported option with Postgres. You will need additional non-volatile cache on your disks to ensure that is a safe option, though that will yield performance advantages also. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] vacuumdb in parallel
On Thu, 2009-10-15 at 09:24 -0300, Alvaro Herrera wrote: > Simon Riggs escribió: > > On Wed, 2009-10-14 at 13:57 -0300, Alvaro Herrera wrote: > > > Anj Adu escribió: > > > > > > > I have several "daily" tables that get dropped every day..Is there a > > > > wildcard that I can use to tell vacuumdb NOT to vacuum those > > > > tables... > > > > > > No. You need to do "INSERT INTO pg_autovacuum" (or ALTER TABLE/SET in > > > 8.4) > > > just after you've created the table. > > > > This doesn't work with vacuumdb, only with autovacuum. > > Ah, that's right. But then I see no reason why autovacuum wouldn't work > for him with this (he doesn't seem to have tried it). IIRC he wanted to do vacuum in parallel using 8.1 That's before you did your fine work to improve autovacuum. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] vacuumdb in parallel
On Wed, 2009-10-14 at 13:57 -0300, Alvaro Herrera wrote: > Anj Adu escribió: > > > I have several "daily" tables that get dropped every day..Is there a > > wildcard that I can use to tell vacuumdb NOT to vacuum those > > tables... > > No. You need to do "INSERT INTO pg_autovacuum" (or ALTER TABLE/SET in 8.4) > just after you've created the table. This doesn't work with vacuumdb, only with autovacuum. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] vacuumdb in parallel
On Tue, 2009-10-13 at 19:40 -0600, Scott Marlowe wrote: > On Tue, Oct 13, 2009 at 7:29 PM, Anj Adu wrote: > > I am running Postgres 8.1.9 on an 8 core Xeon 5430 box that is showing > > single digit CPU and IO utilization. the database size is 820G . > > Vacuum_cost_delay=0 and maintenance_mem = 900M > > > > Is there an option to vacuumdb or a way to make it run parallel threads. > > I think that option showed up in 8.2 or 8.3. There is no option to do that yet at any release. We have parallel pg_restore, but that doesn't apply to vacuumdb, reindexdb, clusterdb or any custom written jobs. You need to break out your top few tables into separate jobs, e.g. vacuumdb -t big1 vacuumdb -t big2 ... -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Warm standby questions
On Tue, 2009-10-13 at 13:17 -0400, David F. Skoll wrote: > Q2. If I stop and restart backup server B while master server A continues > to run, will B continue eating WALs from where it left off? Or do we need > another full backup? (We'll assume WAL shipping continues successfully during > the time B is down: I'm only stopping/restarting PostgreSQL, not the entire > machine.) Recovery is restartable. Not quite from where it left off, but near enough that it won't take too long to get back to where it left off. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] postgres 8.2.9 can't drop database in single user mode
On Wed, 2009-04-22 at 13:31 -0400, Tom Lane wrote: > "Maria L. Wilson" writes: > > Question - is there any maintenance type item that we could to to check > > for uncommitted transactions on a regular basis - outside of the > > pg_prepared_xacts table? > > pg_prepared_xacts is the only SQL-level visibility there is. From a > monitoring standpoint it might be easier to watch for files in the > $PGDATA/pg_twophase/ directory, but that's just a different view of > the same information. We discussed having startup mention there were outstanding two-phase xacts, though it was blocked for some reason. > >How about from a developers position - most of our code accessing > > the databases is jboss/java/jdbc. What could have happened from the > > code side that caused these uncommitted transactions? > > Basically, somebody issued PREPARE TRANSACTION and then walked away > without either committing or rolling back. As a rule it's a bad idea > to use PREPARE TRANSACTION unless you've bought into the whole XA > concept including an external "transaction monitor" that keeps track > of open two-phase transactions across a set of related databases. > > If you don't think that there is anything like that that this DB should > be involved in, you might want to set max_prepared_transactions = 0 > to prevent future mistaken issuances of PREPARE TRANSACTION. > (Bear in mind that you have to restart Postgres to make such a change > take effect.) I think we should include further measures on this: * A command to rollback or commit all prepared transactions: COMMIT PREPARED ALL or ROLLBACK PREPARED ALL (or a function to do this). * If you issue normal COMMIT or ROLLBACK immediately after a PREPARE it says "there is no transaction in progress". It should issue a more sensible warning such as "you used the wrong command". * Same idea, taken further: If you issue anything other than a COMMIT PREPARED or ROLLBACK PREPARED on a session *after* issuing a PREPARE TRANSACTION then it should give an ERROR. If the session continues to exist then the server has not crashed and so it must always be programming error. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Need help to restore database
On Tue, 2009-03-17 at 09:50 +0100, Lori, Giancarlo wrote: > we are facing with a struggle problem with PostgreSql database > production. > > A client system based on pgsql is down because the database raise an > error message like “ no left space on device”. We have deleted some > log files on this machine and after a system restart the db (and the > system) was up&running. After two days the database has crashed again. > Looking the postgres log there was an error on xlog file (I have > inspected the directory pg_xlog and no files I found it). I’ll try to > re-build the xlog file with pg_resetxlog. The utilities has created a > new xlog file, the postmaster process run, but any command pg_dump, > etc I try to execute failed. The system respond that there are not > database in the system. Can anyone help us ? 2ndQuadrant has a lot of experience with helping people out with problems like this, including data recovery from damaged databases. Please let us know if we can be of any help. Apologies for mentioning commercial matters on list, though this work helps fund further developments in this area. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] [GENERAL] postgres deployment
On Tue, 2009-03-10 at 11:17 +0530, Nagalingam, Karthikeyan wrote: >Can I know the effective solution to do the backup and recovery > for Postgres in storage environment. > > Please share Any existing deployment and solution for backup and > recovery for Postgres in storage > > Any suggestion and recommendation are welcome. Postgres has sophisticated techniques for backup and recovery, proven in the field over many years. Please start here http://developer.postgresql.org/pgdocs/postgres/backup.html Integration is possible with 3rd party devices and software. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgressql backup/restore question
On Wed, 2009-03-04 at 17:19 -0500, Tom Lane wrote: > This behavior might be all right for an emergency recovery kind of tool, > but I can't see us considering it a supported feature. I agree post-recovery cleanup would be required to bring up a fully safe read-write database. That's one of the reasons my longer term thoughts are towards running transactions immediately after recovery completes, for other uses also. > The larger point though is that I suspect what the OP really is looking > for is "restore just this one database into my existing cluster, without > breaking the other databases that are already in it". There is zero > chance of ever doing that with a WAL-based backup --- transaction ID > inconsistencies would break it, even without considering the contents > of shared catalogs. Agreed. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgressql backup/restore question
On Wed, 2009-03-04 at 16:27 -0500, Tom Lane wrote: > Simon Riggs writes: > > On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote: > >> It's not as easy as all that. What will you do with updates to shared > >> catalogs? > > > Apply them. > > ... which leaves your other databases in inconsistent states. Which is not a problem if you didn't want to restore them in the first place. You might complain that we would need safeguards to protect people from trying to access non-restored databases and then failing to understand why they aren't there. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgressql backup/restore question
On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote: > Simon Riggs writes: > > On Wed, 2009-03-04 at 15:59 +0200, Peter Eisentraut wrote: > >> samana srikanth wrote: > >>> Can we do a point-in-time restore of a single database out of n > >>> databases??. > >> > >> In principle no. But you could invent workarounds such as recovering to > >> the point where you are happy with your restored one database, and then > >> restore the other n-1 databases from an SQL dump. > > > It is possible, but we just don't currently support it. > > It's not as easy as all that. What will you do with updates to shared > catalogs? Apply them. So: its possible to do shared catalogs plus a subset of other databases. I was assuming that updates to shared catalogs were small overall. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] standby waiting for what?
On Wed, 2009-03-04 at 15:14 -0500, Ray Stell wrote: > On Wed, Mar 04, 2009 at 03:06:12PM -0500, Ray Stell wrote: > > Testing pg_standby in 8.3.6. I've gotten this standby into some sort of > > bind. It seems like it may be waiting for some WAL. How can I tell > > what it is waiting on? I don't really know how this works, so I may > > > say something silly. The standby log says: > > ,2512,,2009-03-04 12:23:01.483 EST,49aeb8f5.9d0,1,2009-03-04 12:23:01 EST,0, > LOG: database system was interrupted; last known up at 2009-03-04 12:20:29 > EST > ,2512,,2009-03-04 12:23:01.483 EST,49aeb8f5.9d0,2,2009-03-04 12:23:01 EST,0, > LOG: starting archive recovery > ,2512,,2009-03-04 12:23:01.484 EST,49aeb8f5.9d0,3,2009-03-04 12:23:01 EST,0, > LOG: restore_command = '/usr/local/pgsql/bin/pg_standby > /data/pgsql/wals/alerts_oamp %f %p %r >> > /home/postgresql/log/alerts_oamp/recovery.log' You've set archive_timeout? http://developer.postgresql.org/pgdocs/postgres/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgressql backup/restore question
On Wed, 2009-03-04 at 15:59 +0200, Peter Eisentraut wrote: > samana srikanth wrote: > > Can we do a point-in-time restore of a single database out of n > > databases??. > > In principle no. But you could invent workarounds such as recovering to > the point where you are happy with your restored one database, and then > restore the other n-1 databases from an SQL dump. It is possible, but we just don't currently support it. My submission on rmgr plugins would have provided this feature though it was rejected as "not wanted". I have code hooks required to do this, if people want to contact me off-list. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Totally inappropriate by-line for a major representative of this organization
On Thu, 2009-02-05 at 17:57 +0100, Sylvain Avril wrote: > I think you should read this and especially article 18 and 19 of > this : > http://www.un.org/Overview/rights.html Interesting read, thanks. OT: I see that Article 27 (2) appears to run directly counter to open source software. And it would appear that paragraph is in direct conflict with paragraph (1) of same article. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Online backups are failing
On Tue, 2009-01-20 at 14:21 -0500, Bhella Paramjeet-PFCW67 wrote: > First we saw this problem on our postgres 8.2.7 database where we had > the "stats_block_level" parameter turned on in postgresql.conf file. > After I turned off stats_block_level parameter, our backups did not > fail. Seems like the stats files are changing while being copied. I'd suggest excluding global/pgstat* from the backup. Stats are reset at recovery anyway, so you've lost nothing. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Warm Standby - log shipping
On Fri, 2008-12-19 at 08:51 -0800, Joshua D. Drake wrote: > On Fri, 2008-12-19 at 09:14 -0500, Mark Steben wrote: > > What I'm hearing is that I have to perform a base backup on my master in > > Mass. after recovery completes, send that over a secure network > > To Virginia, and lay it down there. Simple enough but the time to travel > > Over the network becomes an issue - 12 - 13 hours at best. > > If we have to do this then we will. I just want to make sure I'm > > understanding your advice. > > Yes. Yes, but use rsync, so you don't need to wait for 13 hours. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Warm Standby - log shipping
On Thu, 2008-12-18 at 16:43 -0500, Mark Steben wrote: > 3. I am currently in a state where a log got partially copied and > postgres > cannot find a valid checkpoint to restart. What is the best way to > remedy > this situation? Pg_resetxlog perhaps? Now, pg_resetxlogs, but in future don't delete WAL files that may be needed for recovery. You can use pg_controldata test | grep REDO | cut -d: -f 2 to find out the LSN of the last restartpoint, which can then be converted into an xlogfilename using pg_xlogfile_name(). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] warm standby server
On Wed, 2008-12-10 at 14:23 +0200, [EMAIL PROTECTED] wrote: > Hi, > > I am using pg 8.3 and configured warm standby server. It is working > well, but my db is relative big ( ~ 40 GB). > > Is it possible to stop running "Warm standby server" and when it > starts again to continue executing WALs from the stop moment. Yes > I'm using following command to stop the server: > > kill -SIGINT `head -1 /POSTGRESQL_DATA_DIR/postmaster.pid` Use pg_ctl > but when it starts again there is broken link > "/POSTGRESQL_DATA_DIR/pg_xlog/RECOVERYHISTORY" which points to the > following missing file "/WAL_ARCHIVE_DIR/0001.history" > > i wonder if it is fatal or not? That's normal. > I will not to start the process ot making the base backup and send it > to my warm standby server. My warm standby server is on diferent > location connected via slow connection. That's OK. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Deleting old archived WAL files
On Thu, 2008-10-30 at 16:17 -0400, Chander Ganesan wrote: > Why? If you haven't saved a backup that you made previously (using > pg_start_backup()), having archived wal files is useless. I don't see > why you just don't arbitrarily remove them immediately. > > WAL files are useful when you have a "starting point" (i.e., a backup > made using the PITR backup method), and a set of WAL files generated > "after" the starting point. If you don't preserve the start point > (i.e., the PITR backup) then the WAL files are basically useless. They are still better than nothing at all. I haven't read the whole thread, but suggesting deleting things like that makes me go all twitchy. :-). Do a couple of proper backups and then delete em. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] 8.3.4 PITR recovery questions
On Fri, 2008-10-17 at 15:09 -0500, Kevin Grittner wrote: > In both cases it seemed to take longer to apply the WAL files during > the recovery process than we're used to from 8.2 I don't have hard > numbers, but before I spent time investigating I thought I would ask > here if that is already a known issue. We're doing a lot in 8.4 to improve performance. I'm allowing bgwriter to be active, so that cleaning and restartpoints are handled asynchronously. Koichi is releasing a patch to use readahead on the WAL files. Taken together we might expect recovery to be at least twice as fast on a 2+ CPU system. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Another PITR question
On Wed, 2008-10-22 at 17:09 -0300, Alvaro Herrera wrote: > Scott Whitney wrote: > > > However, I want the standby to CONSTANTLY stay in recovery mode, not > > just restore the WALs it finds when I start it. > > > > Is this possible? Can someone explain or point me to some example docs > > somewhere? > > http://www.postgresql.org/docs/8.3/static/pgstandby.html We should backpatch the docs for 8.2 also. I know we don't usually do that but it would save some time for people. Or at least allow comments to be added to previous doc versions. 8.3 docs have the wording "A working example of a waiting restore_command is provided as a contrib module named pg_standby. It should be used as a reference on how to correctly implement the logic described above. It can also be extended as needed to support specific configurations or environments." -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Strategies for validating WAL-shipping replication
On Mon, 2008-10-20 at 08:31 +0200, Florian Weimer wrote: > We've set up WAL-shipping replication on one of our database servers, > and the question is whether it runs correctly. There's two answers here. The first is that the code used is exactly the same as crash recovery. So if you trust crash recovery you should trust archive recovery, which means WAL shipping. The differences are all about where you start and stop WAL replay. The second is that any form of replication requires testing. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Strategies for validating WAL-shipping replication
On Mon, 2008-10-20 at 01:08 -0700, wstrzalka wrote: > There is not such a possibility. However it's on TODO list: > http://wiki.postgresql.org/wiki/Todo > I'm waiting for this feature too. > > I can understand your fear about the data, that you can not query for > it. Please test the Hot Standby patch... -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
On Tue, 2008-09-23 at 21:05 +0300, Devrim GÜNDÜZ wrote: > Hi, > > On Tue, 2008-09-23 at 09:49 +0100, Simon Riggs wrote: > > I'll look at doing that. We need the SUSE builds also. > > I actually built 8.3.4 on SLES 10.2 on..err..Friday, while building > Fedora/RH RPMs. 8.3.1 spec of SLES is broken IMHO, and it requires > special attention from someone who is experienced in packaging (me :-) ) > and who is familiar with pg code.. > > I fixed spec file. The only remaining issue is libpgport.a. If someone > who is familiar to PG code can comment on how this file is built, I can > push that spec file to my repo, so that Simon or others can continue > maintaining it. That file is built on Fedora/RHEL, but I'm not sure why > SLES skips it. I need that file for Slony-I on SLES,since AFAICS Slony > version is 1.2.6 on SLES :-( > > I don't have access to that SLES server. I'll ask my friend to post the > spec to me, and I'll let you know about the status. Having looked at where we are now, it's going to take more than a little work to get everything in order. Peter had a good go at it, but that was a few releases ago. I've been wanting to make SUSE releases better for a few years now, so am happy to take this on long term. It's not a snap decision since I build on OpenSUSE daily, but from source, not RPMs. As you say, you're in a better position to sort out a stopgap in the short term and I can then take on the task after feature freeze when I'll have more time. In any case, we'll need to make them as consistent as possible between different Linuxes so I'll be relying on your packaging experience. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
On Tue, 2008-09-23 at 10:05 +0100, Dave Page wrote: > On Tue, Sep 23, 2008 at 9:49 AM, Simon Riggs <[EMAIL PROTECTED]> wrote: > > >> Oh, I thought you were looking after that build. If it's not being > >> maintained, we'll need to remove it from the download pages unless > >> someone else can volunteer? > > > > I'll look at doing that. We need the SUSE builds also. > > Great, thanks Simon. If you can commit to ongoing maintenance then > we'll get you on the packagers list so you can get advance notice of > releases and having input on the scheduling details. Well, strangely, committing to ongoing maintenance will be easier than doing the first one. ;-) -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
On Tue, 2008-09-23 at 09:06 +0100, Dave Page wrote: > 2008/9/23 Peter Eisentraut <[EMAIL PROTECTED]>: > > Gerd König wrote: > >> > >> a few months ago we started using Postgres on Opensuse10.3-64bit. > >> We installed Postgres 8.3.1 with the (at that time) latest available > >> rpm's. > >> But now Postgres' current version is 8.3.4 and I'm wondering why there > >> are no new rpm's for Opensuse ?!?! > > > > The answer is quite simply that no one is volunteering to do the work. > > Oh, I thought you were looking after that build. If it's not being > maintained, we'll need to remove it from the download pages unless > someone else can volunteer? I'll look at doing that. We need the SUSE builds also. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PITR with MS-DOS shell
On Fri, 2008-08-29 at 12:31 -0700, Richard Broersma wrote: > Reading the manual for PITR, I see the backup commands are tar, cpio, and > rsync. > > What DOS shell commands recommended: copy, xcopy? Is there something better? Any command that copies files. Or an integrated backup product. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PITR wrm-standby startup fails
On Sun, 2008-08-24 at 22:17 -0700, Bob Lunney wrote: > I'm trying to vet the PITR/warm-standby process so set up a primary > and secondary server on two different Windows machines. (Yeah, I > know, but I don't have a choice in the matter.) > > The primary works fine and copies its WAL files to the archive > directory. As long as the secondary is in recovery mode life is good. > When I introduce the trigger file, however, to kick the secondary out > of recovery mode it bombs, complaining that its looking for the next > WAL file (which is never going to arrive, especially not in the local > pg_xlog directory), then gives me an "invalid parameter" error. The > relevent part of the secondary's log file is here: > > 2008-08-24 23:02:56 CDT LOG: restored log file > "000100040088" from archive > 2008-08-24 23:03:01 CDT LOG: restored log file > "000100040089" from archive > 2008-08-24 23:03:06 CDT LOG: restored log file > "00010004008A" from archive > 2008-08-24 23:07:02 CDT LOG: could not open file > "pg_xlog/00010004008B" (log file 4, segment 139): No such > file or directory > 2008-08-24 23:07:05 CDT LOG: startup process (PID 1468) was > terminated by exception 0xC00D > 2008-08-24 23:07:05 CDT HINT: See C include file "ntstatus.h" for a > description of the hexadecimal value. > 2008-08-24 23:07:05 CDT LOG: aborting startup due to startup process > failure > > My recovery command is: > > restore_command = '..\bin\pg_standby -d -s 5 > -t .\pgsql.trigger.5442 ..\data\archive %f %p %r 2>>pg_log > \standby.log' > > > I found this on the forums. Could it be the source of the problem I'm > experiencing? The way you are using this looks correct. The startup process would not/should not fail in this way and there should be additional messages between the log entries from 23:03 to 23:07. I don't see any reason for a 4 minute wait at that point, especially since previous files took 5 seconds. Are you showing us the whole log? Please re-run this at DEBUG3 so we can see what else is happening. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] New postgres installation
On Fri, 2008-08-15 at 09:44 -0400, Carol Walter wrote: > All the documentation I've read says that for Unix installations > should compile source. You're right. We should fix that so it also describes using pre-packaged options also. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pitr standby on slave with skewed time
On Fri, 2008-07-11 at 12:15 -0400, Tom Lane wrote: > Robert Treat <[EMAIL PROTECTED]> writes: > > Was review a clients config/setup and ran across a pitr warm standby > > scenario > > where the master machine is set to the current time, but the slave's time > > is > > currently sitting back in the month of May. Outside of getting ntp setup on > > the machine, I am wondering if I need to do anything special with the > > postgresql setup, or if just setting the correct date on the machine is a > > safe enough operation that nothing else would need to be done (like > > re-doing > > the base backup). Any thoughts? > > AFAIR you should be all right ... PITR only looks at WAL indexes, not > file timestamps. WAL filenames, just in case anybody listening thinks "I didn't create an index on my WAL, should I?". This is so people that set their pg_xlog filesystem no file modification timestamps don't screw up their recovery. > The slave does watch the current time to decide when to do recovery > restartpoints, so if you were setting the clock *back* by a large amount > it might be wise to stop and restart the slave postmaster. Forward > should be no problem though. Yeh, you're good. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Warm standby server
On Thu, 2008-06-26 at 13:01 -0500, Scott Whitney wrote: > A 2nd question: Is it possible to have 2 standby servers with a single > master duplicating to standby1 (at my coloc), and standby2 (at my office)? > Assume no auto-failover. Yes, that works too. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Warm standby server
On Thu, 2008-06-26 at 10:19 -0500, Scott Whitney wrote: > I've got 3 different database servers (db01, db02 and db03). > > I would like to have a WAL standby server that replays logs for all 3 in > case one goes down, so I can promote that particular server. > > Can I do this by installing 3 separate postmasters on this machine? > Obviously, if 2 went down at the same time, I'd have to do some magic to > bring up another machine, but I'm not sure that's a concern. Yes, that will work. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Warm-standby in 8.2
On Thu, 2008-06-19 at 19:31 -0400, Bhella Paramjeet-PFCW67 wrote: > We have created a postgres database with a warm-standby in postgres 8.2 > following the document on the archive by Charles Duffy. Please can you read the main docs? The above document is out of date. If there is something not mentioned there we will update them. Thanks, -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] error message in PITR restore:
On Fri, 2008-05-16 at 13:21 -0400, Mark Steben wrote: > Simon, I think you uncovered the problem but I don't see anyway around > it Short of reloading the db from a pg_dump backup. Any suggestions > would Be appreciated. Thanks, Re-run the backup? Sounds safest. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] error message in PITR restore:
On Fri, 2008-05-16 at 11:35 -0400, Mark Steben wrote: > I’m in the process of implementing PITR backups and restores and > learning as I practice. I restored > > Using tar –xzf (backup-name) followed by restarting postgres and > having the server replay the logs. > > I used a recovery_target_time of 1 day prior to current_date > Postgres seems to come up OK but > Did you use pg_start_backup()? That sequence of events can occur if the backup_label file is missing, since that won't constrain the stop point from being earlier than it should be. Is there a backup_label file as part of the tar? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] what is the maximum number of rows in a table in postgresql 8.1
On Tue, 2008-03-25 at 17:42 +0530, sathiya psql wrote: > > > I am not sure I understand your question. maybe this link > answers > your question: > http://www.postgresql.org/about/ > this i know. > > this is the documentation limitations... > > i need what will be the maximum number of rows to have good > performance in postgres 8.1 You want a simple answer to a very difficult question and regrettably it isn't that simple or that quick. Some of the considerations you would need to think about would be: * What workloads are you running? * What hardware will it run on? * What performance is required? ... -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] DB Structure Recovery
On Wed, 2008-03-19 at 16:56 +0200, veejar wrote: > My filesystem (UFS-FreeBSD) was crashed and I have lost files from > PGSQL DATA DIR FOLDER after fsck-utility. > > I have lost files from directory "pg_xlog" > I reset my pg_xlog. > > And now have such error by starting pgsql-server: > > LOG: database system was interrupted at 2008-03-18 22:29:48 EET > LOG: checkpoint record is at 3E/6C20 > LOG: redo record is at 3E/6C20; undo record is at 3E/6C20; > shutdown TRUE > LOG: next transaction ID: 0/1624775486; next OID: 9528514 > LOG: next MultiXactId: 6643; next MultiXactOffset: 13690 > LOG: database system was not properly shut down; automatic recovery in > progress > LOG: record with zero length at 3E/6C68 > LOG: redo is not required > FATAL: could not access status of transaction 6643 > DETAIL: Could not read from file "pg_multixact/offsets/" at > offset 24576: Success. > LOG: startup process (PID 6267) exited with exit code 1 > LOG: aborting startup due to startup process failure > LOG: logger shutting down > > Is it possible to lose this problem? > HELP!!! It is possible to recover some data in most situations, but it can take anywhere from a day or two. My company offers database recovery services, if you're willing to pay. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] System in Recovery Mode
On Mon, 2008-02-25 at 09:53 -0600, Aaron Bono wrote: <2008-02-25 07:15:24 CST username 127.0.0.1>ERROR: unrecognized node type: 4260493 > ... > <2008-02-25 07:15:49 CST username 127.0.0.1>CONTEXT: PL/pgSQL > function "get_branch_for_zip" line 61 at fetch ... > <2008-02-25 07:15:56 CST >LOG: could not truncate directory > "pg_multixact/members": apparent wraparound Some interesting failures there. Is this a modified Postgres server? Are you running externally supplied C language functions? Have you had hardware problems? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] System in Recovery Mode
On Mon, 2008-02-25 at 08:11 -0600, Aaron Bono wrote: > Our database was going along just fine this morning when, out of the > blue, we started getting the following errors: > > FATAL: the database system is in recovery mode > > We are using PostgreSQL 8.1.3 on CentOS. I was the only person on the > machine and had recently started a small batch that had just finished > querying and updating the database. This batch runs constantly and > has not been changed in months. Your database has crashed and is recovering. You should look at the server log to find out when and why this happened, then either correct the problem or report the bug. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton?
On Tue, 2008-02-19 at 09:53 +0530, Praveen Kumar (TUV) wrote: > Go through this below mentioned link > > > > http://archives.postgresql.org/sydpug/2006-10/msg1.php I think people should be reading the manual, rather than reading an old email. We have improved and corrected the manual, but old emails stay the same. We can also change the manual some more in response to clarifications. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [ADMIN] Backup
On Thu, 2008-01-31 at 10:02 -0500, Chander Ganesan wrote: > Magnus Hagander wrote: > > On Thu, Jan 31, 2008 at 03:34:05PM +0100, Martijn van Oosterhout wrote: > > > >> On Thu, Jan 31, 2008 at 01:28:48PM +, Simon Riggs wrote: > >> > >>> That sentence has no place in any discussion about "backup" because the > >>> risk is not just a few transactions, it is a corrupt and inconsistent > >>> database from which both old and new data would be inaccessible. > >>> > >> Hmm? I thought the whole point of a filesystem snapshot was that it's > >> the same as if the system crashed. And I was fairly sure we could > >> recover from that... > >> > > > > That was my assumption as well. *Assuming* that the filesystem snapshot is > > consistent. There are a bunch of solutions that don't do consistent > > snapshots between different partitions, so if your WAL or one tablespace is > > on a different partition, you'll get corruption anyway... (seen this in > > Big Commercial Database, so that's not a pg problem) > > > Agreed. That's why I made it a point to mention that all of your > tablespaces should be on the same file system... In hindsight, I should > have also stated that your WAL logs should be on the same file system as > well... I think we all understand and agree, I just start twitching when anyone talks about it being OK to lose transactions when backing up. You meant the ones currently in progress, not the ones already committed and on disk. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [GENERAL] [ADMIN] Backup
On Thu, 2008-01-31 at 12:09 -0300, Alvaro Herrera wrote: > > Simon Riggs wrote: > > >> As far as I am concerned, if any Postgres user loses data then we're all > >> responsible. > > Remember, our license says this software is given without any warranty > whatsoever, implicit or explicit, written or implied, given or sold, > alive or deceased. Yes! ...I meant via the free press, not via the courts. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [ADMIN] Backup
On Thu, 2008-01-31 at 07:21 -0500, Chander Ganesan wrote: > If you don't mind if you lose some transactions That sentence has no place in any discussion about "backup" because the risk is not just a few transactions, it is a corrupt and inconsistent database from which both old and new data would be inaccessible. As far as I am concerned, if any Postgres user loses data then we're all responsible. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Backup
On Fri, 2008-01-25 at 11:34 +1100, Phillip Smith wrote: > > We have a center in Europe who has just started to use PostgreSQL and was > > asking me if there are any Symantec product or other products that backup > > this type of database. > > It doesn't appear to. The design of the PITR system allows a product-agnostic backup. Anything that can backup a file can backup PostgreSQL. There is no need for special certifications of hologram logos. You may need to write a few lines of script to do it, but that's not a problem surely? So you can use pg_dump or PITR, as you choose. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] WAL copying includes vacuum, reindex, etc?
On Tue, 2008-01-15 at 09:19 -0800, David Wall wrote: > When running WAL backups and warm-standby restores (we're currently on > PG 8.2), do the vacuumlo changes, vacuum, analyze and reindex type > commands get pushed through WAL files to the backup? Or will the backup > database not have these maintenance items done? All of those commands produce WAL for critical changes to data blocks, so those are passed through correctly. Hint bits are not set following recovery, so a full database VACUUM may help later performance, depends upon your access patterns. It's probably good for peace of mind too, but it's a long way from being an essential requirement. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] pg_start_backup without WAL archiving
On Sun, 2007-11-25 at 15:24 +0100, Peter Eisentraut wrote: > Why is it not allowed to call pg_start_backup when WAL archiving is off? > Wouldn't this be useful as a discrete file system backup without PITR > capability? I'm submitting a patch today that better describes PITR facilities for making a standalone backup. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Consistent PITR backup
On Thu, 2007-10-11 at 16:37 +0200, Thomas Karcher wrote: > Hi Tom, > > > > My idea is now: When doing a consistent PITR backup, extend the > > > documented steps between 4 (pg_stop_backup()) and 5 (wait for archived > > > WAL file) with a busy wait on pg_xlog/archive_status/*.ready files - as > > > soon as no .ready files are there anymore, the archiver has done its > > > work, and I can safely backup the archived WAL files in order to have a > > > really consistent backup. > > If the database is busy, you might never see an instant where there are > > no .ready files. Certainly it's possible that this approach will > > archive many more segments than you need. > > I see - but the risk for never seeing an instant without .ready files is > relatively slow, I hope. And compared to the risk of not having a > consistent backup, I could live with having backed up more WAL files > than I actually would have needed. There will be gaps, otherwise the archiver will never catch up over time, but its hard to say when those might be. > > > Is that right? Or is there an easier way to do it? > > I believe the intended way to do this is to look at pg_stop_backup's > > return value to determine the last segment you need to archive. > > Thank you - I saw that, but I didn't have a clue about how to determine > the WAL filename in which this segment is to be found ... Look at pg_stop_backup() in the docs. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Using rsync for base backups for PITR
On Wed, 2007-10-10 at 11:46 -0500, Scott Whitney wrote: > Is rsync a supported method for a warm standby server? Specifically, I'm > thinking about: > http://www.taygeta.com/ha-postgresql.html That's dated 2001... -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Executing external program from stored procedure
On Thu, 2007-10-04 at 20:02 -0600, Benjamin Krajmalnik wrote: > Is there a way to execute an external program from within a stored > procedure? Write a Function in C -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Restarting standby database have to reaply WAL (redo logs)
On Tue, 2007-09-25 at 14:14 -0300, Cesar Otero wrote: > I think that after restarting standby db the first log should be last > log applied + 1 That isn't how it works, for performance reasons. We don't make a restartpoint after each file, we do this on the first usable checkpoint WAL record that we find after checkpoint_timeout seconds. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Restarting standby database have to reaply WAL (redo logs)
On Tue, 2007-09-25 at 12:41 -0300, Cesar Otero wrote: > Then shutdown standby > > ## > WAL file not present yet. Checking for trigger file...2007-09-25 > 12:31:47 ARTLOG: received fast shutdown request > 2007-09-25 12:31:47 ARTFATAL: could not restore file > "0001001800A6" from archive: return code 15 > 2007-09-25 12:31:47 ARTLOG: startup process (PID 29654) exited with exit > code 1 > 2007-09-25 12:31:47 ARTLOG: aborting startup due to startup process failure > 2007-09-25 12:31:47 ARTLOG: logger shutting down The shutdown takes down all processes, so the current restore fails in mid-flight. That's how it works, currently. > And start standby again > Trigger file: /var/lib/postgres/procuracion/standby.trigger > Waiting for WAL file: > /var/lib/postgres/archive/procuracion/00010018009D > WAL file path : 00010018009D > Restoring to... : pg_xlog/RECOVERYXLOG > Sleep interval : 5 seconds > Max wait interval : 0 forever > Command for restore : cp > "/var/lib/postgres/archive/procuracion/00010018009D" > "pg_xlog/RECOVERYXLOG" Then we restart recovery from the last restartpoint. Is that what you meant? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
