Re: [GENERAL] Pgbasebackup help
Thanks David, This helped me to understand the WAL importance. Finally, we decided to use "stream" option to copy the WAL file during the backup as mentioned in the help. Enabled this options in postgres.conf wal_level = hot_standby max_wal_senders = 2 Though I get the information, which are archived during the backup process. I myself deleting archived records the after time T3, while restoring the backup by using our sql procedures. Since I am not using archive recovery or standy replica (I am restoring the data folder in to the same server not in standby server), I can't use the recovery.conf options to recover till Time T3. Hence I forcefully deleted as mentioned earlier. Please let me know If any way to replay the WAL till Time T3 then I am interested to use it. Thanks once again. Regards, Ramkumar. -Original Message- From: David Steele [mailto:da...@pgmasters.net] Sent: Friday, December 04, 2015 6:26 PM To: Yelai, Ramkumar IN BLR STS; pgsql-general@postgresql.org Subject: Re: [GENERAL] Pgbasebackup help On 12/3/15 12:59 AM, Yelai, Ramkumar IN BLR STS wrote: > What I wanted to achieve is simple copy of Data folder. I can't shutdown the > database during the backup and unable to use file system copy of data folder > as it creates inconsistency and don't want to use pg_dump. > > Hence I decided to use Pg_basebackup for copying the base backup and don't > want to replay the wal. Replaying WAL is *not* optional. Each restore will have to replay at least one WAL segment to become consistent, depending on write volume during the backup. > Anyway, pg_basebackup put checkpoint before copying the data folder. For me > it is enough to restore till checkpoint. This won't work - the database keeps running and making changes after the checkpoint. > I saw this link > http://blog.veritech.io/2014/10/automated-backup-for-postgresql-cluster.html. > > In this link also, I have not seen they have enabled archive_mode. Archive > mode is not necessary as long as you streaming the your wal files to pg_xlog. These instructions are for bringing up a replica. Even if this is OK for your purposes, it still would not get you a database at time T3. You are supposing that because this method does not use archiving that > Also, even if I have all wal files , how do I restore till time T3. I am > analyzing at pgbackrest to know how to restore backup till time T3. To restore to time T3 you would select a backup that ended *before* T3 then using point-in-time recovery to play forward to T3. That should be explained pretty clearly in the user guide - if there's something you don't understand then it would be helpful to know so I can improve the guide. -- -David da...@pgmasters.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] bdr manual cleanup required
I am trying to repair a broken bdr cluster setup and so far everything I tried failed. Under the original node that ran bdr.bdr_group_create I am getting the following error: 2015-12-04 19:34:29.063 UTC,,,22991,,5661eac4.59cf,1,,2015-12-04 19:34:28 UTC,3/0,0,ERROR,55000,"previous init failed, manual cleanup is required","Found bdr.bdr_nodes entry for bdr (6224504646761731677,1,16389,) with state=i in remote bdr.bdr_nodes","Remove all replication identifiers and slots corresponding to this node from the init target node then drop and recreate this database and try again",,,"bdr (6224504646761731677,1,16389,): perdb" Is there a way to get the cluster in a correct state without having to drop the db? Thanks -Selim
Re: [GENERAL] BDR: ALTER statement hanging
Yes, bdr_connections had the same number of rows: deliver=# select * from bdr.bdr_connections; conn_sysid | conn_timeline | conn_dboid | conn_origin_sysid | conn_origin_timeline | conn_origin_dboid | conn_is_unidirectional | conn_dsn| conn_apply_delay | conn_replication_sets -+---++---+--+---++--- -+--+--- 6212648563684174798 | 1 | 533136 | 0 | 0 | 0 | f | host=pe-deliverdb-sf-01v port=5432 dbname=deliver user=deliver_admin password=x | | {default} 6223770712502831127 | 1 | 16389 | 0 | 0 | 0 | f | host=pe-deliverdb-sing-01v port=5432 dbname=deliver user=deliver_admin password=x | | {default} 6223800735012265413 | 1 | 16389 | 0 | 0 | 0 | f | host=pe-deliverdb-lon-01v port=5432 dbname=deliver user=deliver_admin password=x | | {default} (3 rows) One other thing I noticed is that the conn_dboid is the same for two of the nodes. Is that normal? -Selim From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Sylvain MARECHAL [marechal.sylva...@gmail.com] Sent: Friday, December 04, 2015 10:14 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] BDR: ALTER statement hanging Le 04/12/2015 18:59, Andreas Kretschmer a écrit : > > > I think, the state 'i' is the main reason for your problem, because of: "i- > Joining: The node is doing initial slot creation or an initial dump and load". > > But i can't tell you why this nodes are in this state. > > > Regards, Andreas > > Did-you check the bdr.bdr_connections table? It should have as many lines as the bdr.bdr_nodes tables. Sylvain -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BDR: ALTER statement hanging
Thanks, I removed the other nodes from bdr.bdr_nodes table, deleted all the bdr_connections and pg_replication_identifier entries, dropped the pg_replication_slots restarted the instance and then trying the ALTER statement resulted in: ERROR: No peer nodes or peer node count unknown, cannot acquire DDL lock HINT: BDR is probably still starting up, wait a while The only way I could issue the statement is run the following to convert the node to a standalone instance: BEGIN; SET LOCAL bdr.permit_unsafe_ddl_commands = true; SET LOCAL bdr.skip_ddl_locking = true; security label for 'bdr' on database deliver is '{"bdr": false}'; COMMIT; I am still puzzled as to why the bdr_nodes node_status was reporting "i" when there were no errors in the logs. -Selim From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Andreas Kretschmer [andr...@a-kretschmer.de] Sent: Friday, December 04, 2015 9:59 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] BDR: ALTER statement hanging > Selim Tuvi hat am 4. Dezember 2015 um 18:46 geschrieben: > > > Yes they seem to be active: > > deliver=# select * from pg_replication_slots; > slot_name | plugin | slot_type | datoid | > database | active | xmin | catalog_xmin | restart_lsn > --++---++--++--+--+- > bdr_533136_6223770712502831127_1_16389__ | bdr| logical | 533136 | > deliver | t | | 182302 | 0/9C8A5598 > bdr_533136_6223800735012265413_1_16389__ | bdr| logical | 533136 | > deliver | t | | 182302 | 0/9C8A5598 > (2 rows) > > Although when I look at bdr.bdr_nodes I see the status as still initializing > for the other two nodes, I don't know if that could cause this problem: > > deliver=# select * from bdr.bdr_nodes; > node_sysid | node_timeline | node_dboid | node_status | > node_name | > node_local_dsn > | > node_init_from_dsn > -+---++-+-+--- > -+-- > 6212648563684174798 | 1 | 533136 | r | > pe-deliverdb-sf-01v | host=pe-deliverdb-sf-01v port=5432 dbname=deliver > user=deliver_admin password=x | > 6223770712502831127 | 1 | 16389 | i | > pe-deliverdb-sing-01v | host=pe-deliverdb-sing-01v port=5432 dbname=deliver > user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432 > dbname=deliver user=deliver_admin password=x > 6223800735012265413 | 1 | 16389 | i | > pe-deliverdb-lon-01v | host=pe-deliverdb-lon-01v port=5432 dbname=deliver > user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432 > dbname=deliver user=deliver_admin password=x > > -Selim > I think, the state 'i' is the main reason for your problem, because of: "i- Joining: The node is doing initial slot creation or an initial dump and load". But i can't tell you why this nodes are in this state. Regards, Andreas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BDR: ALTER statement hanging
Le 04/12/2015 18:59, Andreas Kretschmer a écrit : I think, the state 'i' is the main reason for your problem, because of: "i- Joining: The node is doing initial slot creation or an initial dump and load". But i can't tell you why this nodes are in this state. Regards, Andreas Did-you check the bdr.bdr_connections table? It should have as many lines as the bdr.bdr_nodes tables. Sylvain -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BDR: ALTER statement hanging
> Selim Tuvi hat am 4. Dezember 2015 um 18:46 geschrieben: > > > Yes they seem to be active: > > deliver=# select * from pg_replication_slots; > slot_name | plugin | slot_type | datoid | > database | active | xmin | catalog_xmin | restart_lsn > --++---++--++--+--+- > bdr_533136_6223770712502831127_1_16389__ | bdr| logical | 533136 | > deliver | t | | 182302 | 0/9C8A5598 > bdr_533136_6223800735012265413_1_16389__ | bdr| logical | 533136 | > deliver | t | | 182302 | 0/9C8A5598 > (2 rows) > > Although when I look at bdr.bdr_nodes I see the status as still initializing > for the other two nodes, I don't know if that could cause this problem: > > deliver=# select * from bdr.bdr_nodes; > node_sysid | node_timeline | node_dboid | node_status | > node_name | > node_local_dsn > | > node_init_from_dsn > -+---++-+-+--- > -+-- > 6212648563684174798 | 1 | 533136 | r | > pe-deliverdb-sf-01v | host=pe-deliverdb-sf-01v port=5432 dbname=deliver > user=deliver_admin password=x | > 6223770712502831127 | 1 | 16389 | i | > pe-deliverdb-sing-01v | host=pe-deliverdb-sing-01v port=5432 dbname=deliver > user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432 > dbname=deliver user=deliver_admin password=x > 6223800735012265413 | 1 | 16389 | i | > pe-deliverdb-lon-01v | host=pe-deliverdb-lon-01v port=5432 dbname=deliver > user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432 > dbname=deliver user=deliver_admin password=x > > -Selim > I think, the state 'i' is the main reason for your problem, because of: "i- Joining: The node is doing initial slot creation or an initial dump and load". But i can't tell you why this nodes are in this state. Regards, Andreas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BDR: ALTER statement hanging
Yes they seem to be active: deliver=# select * from pg_replication_slots; slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn --++---++--++--+--+- bdr_533136_6223770712502831127_1_16389__ | bdr| logical | 533136 | deliver | t | | 182302 | 0/9C8A5598 bdr_533136_6223800735012265413_1_16389__ | bdr| logical | 533136 | deliver | t | | 182302 | 0/9C8A5598 (2 rows) Although when I look at bdr.bdr_nodes I see the status as still initializing for the other two nodes, I don't know if that could cause this problem: deliver=# select * from bdr.bdr_nodes; node_sysid | node_timeline | node_dboid | node_status | node_name | node_local_dsn | node_init_from_dsn -+---++-+-+--- -+-- 6212648563684174798 | 1 | 533136 | r | pe-deliverdb-sf-01v | host=pe-deliverdb-sf-01v port=5432 dbname=deliver user=deliver_admin password=x | 6223770712502831127 | 1 | 16389 | i | pe-deliverdb-sing-01v | host=pe-deliverdb-sing-01v port=5432 dbname=deliver user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432 dbname=deliver user=deliver_admin password=x 6223800735012265413 | 1 | 16389 | i | pe-deliverdb-lon-01v | host=pe-deliverdb-lon-01v port=5432 dbname=deliver user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432 dbname=deliver user=deliver_admin password=x -Selim From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Andreas Kretschmer [akretsch...@spamfence.net] Sent: Thursday, December 03, 2015 10:49 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] BDR: ALTER statement hanging Selim Tuvi wrote: > Hi, I am running a three node BDR cluster. BDR version is 0.9.3. Postgres > version is 9.4.5. > > With 0.9.2, I used to be able to issue ALTER statements using psql and it > would > go through. This time it is just hanging. The statement is this: for ddl-commands all nodes MUST be active in replication, so have you checked that in pg_replication_slots? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table with invalid page blocks
Someone has some way of identifying all invalid blocks of a table postgresql ? Plpgsql a function, a tool, somehow. I found one solution on http://www.postgresql.org/message-id/1184245756.24101.178.ca...@coppola.muc.ecircle.de, but I can not change in order to identify any defective blocks at once. Postgresql 9.3.10 x64 Red Hat 7 x64 T.'.A.'.F.'., *Gerdan Rezende dos Santos * +55 (61) 9645-1525
[GENERAL] Table with invalid page blocks
Someone has some way of identifying all invalid blocks of a table postgresql ? Plpgsql a function, a tool, somehow. I found one solution on http://www.postgresql.org/message-id/1184245756.24101.178.ca...@coppola.muc.ecircle.de, but I can not change in order to identify any defective blocks at once. Postgresql 9.3.10 x64 Red Hat 7 x64 T.'.A.'.F.'., *Gerdan Rezende dos Santos * +55 (61) 9645-1525
[GENERAL] question about replication slots
Hi @ll, imagine a streaming replication using physical replication slots. And sometime a fail over. All okay. I take a basebackup and rebuild the old master as slave. Is there a risk that the new slave contains active replication slots but no listener on it? What have i to consider? Thx. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pgbasebackup help
On 12/3/15 12:59 AM, Yelai, Ramkumar IN BLR STS wrote: What I wanted to achieve is simple copy of Data folder. I can't shutdown the database during the backup and unable to use file system copy of data folder as it creates inconsistency and don't want to use pg_dump. Hence I decided to use Pg_basebackup for copying the base backup and don't want to replay the wal. Replaying WAL is *not* optional. Each restore will have to replay at least one WAL segment to become consistent, depending on write volume during the backup. Anyway, pg_basebackup put checkpoint before copying the data folder. For me it is enough to restore till checkpoint. This won't work - the database keeps running and making changes after the checkpoint. I saw this link http://blog.veritech.io/2014/10/automated-backup-for-postgresql-cluster.html. In this link also, I have not seen they have enabled archive_mode. Archive mode is not necessary as long as you streaming the your wal files to pg_xlog. These instructions are for bringing up a replica. Even if this is OK for your purposes, it still would not get you a database at time T3. You are supposing that because this method does not use archiving that Also, even if I have all wal files , how do I restore till time T3. I am analyzing at pgbackrest to know how to restore backup till time T3. To restore to time T3 you would select a backup that ended *before* T3 then using point-in-time recovery to play forward to T3. That should be explained pretty clearly in the user guide - if there's something you don't understand then it would be helpful to know so I can improve the guide. -- -David da...@pgmasters.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pgbasebackup help
On 12/2/15 6:25 PM, Jim Nasby wrote: On 12/2/15 1:56 PM, David Steele wrote: >Also, I don’t want enable archive_mode = on as it needs to maintain >archives files. As it turns out, archiving would be the solution to your problem. If you were archiving you could restore a*previous* backup and then replay WAL to exactly T3. There might be some jitter from clock differences but it should have the desired effect. And in this case previous could be a PG backup taken immediately before the backup of the private DBMS. Exactly. I don't remember off-hand if pg_basebackup has an option for delaying the pg_stop_backup() call, but if it does then I think the simplest thing is to just delay that until after your private DBMS backup is complete. There's no option to delay it in pg_basebackup but it can be called whenever you like when using pg_start/stop_backup. So, yeah, if the postgres backup were started first then pg_stop_backup() could be called right after the private backup stops. That would make them pretty much in sync. Of course, it's a lot of work to get that going. pg_basebackup does a lot of work for you. -- -David da...@pgmasters.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC and inet type
On Fri, 4 Dec 2015 09:41:24 + Tim Smith wrote: > When I use "preparedStatement.setString(5,ip);" to send values to a > stored function, it obviously gets sent to postgres as "character > varying". > > Postgres obviously complains loudly and says " Hint: No function > matches the given name and argument types. You might need to add > explicit type casts.". > > What is the appropriate workaround ? You can define param 5 as varchar in your query, as Rob suggests: CREATE FUNCTION some_function(int, int, int, int, int, varchar) ... Then cast the 5th parameter to INET within your function. You can also cast the value in your SQL. sql = "SELECT some_function($, $, $, $, $::INET)"; ... You could also create an Inet class in Java and implement the SQLData interface, then use setObject() instead of setString(). It doesn't appear as if anyone has done this yet, but it would be nice if it were incluced in the JDBC driver. The first answer is probably best for stored procedures, as it simplifies things down the road. The second solution is more universal, as it works for non-function-calling SQL as well. The third solution is probably _really_ the correct one, from a pedantic standpoint, but it's a bit more work to implement. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC and inet type
On Fri, 2015-12-04 at 09:41 +, Tim Smith wrote: > Hi, > > When I use "preparedStatement.setString(5,ip);" to send values to a > stored function, it obviously gets sent to postgres as "character > varying". > > Postgres obviously complains loudly and says " Hint: No function > matches the given name and argument types. You might need to add > explicit type casts.". > > What is the appropriate workaround ? > > Thanks! > > Tim > > "preparedStatement.setString(5,ip);" will set parameter number five to the string held in the variable. That's what setString does. It expects a string to be passed. Your function's fifth IN argument needs to be defined as a "string". Within the function you will have to cast it to inet. HTH, Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plperlu stored procedure seems to freeze for a minute
On 2015-12-03 10:02:18 -0500, Tom Lane wrote: > "Peter J. Holzer" writes: > > Can those signals be safely ignored? Just blocking them (so that they > > are delivered after the UDF finishes) might be safer. But even that may > > be a problem: If the UDF then executes some SQL, could that rely on > > signals being delivered? I have no idea. > > The minute you start fooling with a backend's signal behavior, we're > going to politely refuse to support whatever breakage you run into. As I understood Jim he was talking about possible changes to postgresql to shield UDFs from those signals, not something the author of a UDF should do. > We aren't sending those signals just for amusement's sake. Right. That's why I was sceptical whether those signals could be ignored. I wouldn't have thought so, but Jim clearly knows a lot more about the inner workings of postgresql than I do (which is easy - I know almost nothing) and maybe he knows of a way (something like "we can ignore signals while executing the UDF and just assume that we missed at least one signal and call the magic synchronize state function afterwards") hp -- _ | Peter J. Holzer| I want to forget all about both belts and |_|_) || suspenders; instead, I want to buy pants | | | h...@hjp.at | that actually fit. __/ | http://www.hjp.at/ | -- http://noncombatant.org/ signature.asc Description: Digital signature
[GENERAL] JDBC and inet type
Hi, When I use "preparedStatement.setString(5,ip);" to send values to a stored function, it obviously gets sent to postgres as "character varying". Postgres obviously complains loudly and says " Hint: No function matches the given name and argument types. You might need to add explicit type casts.". What is the appropriate workaround ? Thanks! Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general