Re: [GENERAL] Logical Replication - test_decoding - unchanged-toast-datum

2017-09-27 Thread Andres Freund
On 2017-09-28 08:19:08 +0800, Craig Ringer wrote: > This is one of the MANY reasons test_decoding isn't suitable as the base > for a replication solution. It has "test" in its name for a reason. FWIW, I don't see why the unchanged toast stuff is that. It's clearly discernible from actual datums,

Re: [GENERAL] Logical Replication - test_decoding - unchanged-toast-datum

2017-09-27 Thread Craig Ringer
On 26 September 2017 at 05:01, Abhinav Singh wrote: > Hello, > > I am currently using PostgreSQL Community version 9.4.9 and then using > this instance, I am doing logical replication(using replication slots). I > have created the replication slots using the following

Re: [GENERAL] Rsync to a recovering streaming replica?

2017-09-27 Thread Scott Mead
On Wed, Sep 27, 2017 at 4:08 PM, Igor Polishchuk wrote: > Scott, > Thank you for your insight. I do have some extra disk and network > throughput to spare. However, my question is ‘Can I run rsync while > streaming is running?’ > Ahh, I see. Sorry You need to stop the

Re: [GENERAL] Rsync to a recovering streaming replica?

2017-09-27 Thread Igor Polishchuk
Scott, Thank you for your insight. I do have some extra disk and network throughput to spare. However, my question is ‘Can I run rsync while streaming is running?’ A streaming replica is a physical copy of a master, so why not. My concern is a possible silent introduction of some block

Re: [GENERAL] pg_upgrade?: Upgrade method from/to any version on random OS?

2017-09-27 Thread David G. Johnston
On Wed, Sep 27, 2017 at 12:48 PM, Hans Schou wrote: > I have looked through > https://www.postgresql.org/docs/9.6/static/pgupgrade.html > but it seems more complicated than necessary. > ​[perform dump/restore]​ It went very good but took 100 minutes - where we had

[GENERAL] pg_upgrade?: Upgrade method from/to any version on random OS?

2017-09-27 Thread Hans Schou
If I ask this question without mention the PG version I upgrade from and to, and don't mention the operating system, and don't mention replication, would the standard answer be: Use pg_upgrade! I have looked through https://www.postgresql.org/docs/9.6/static/pgupgrade.html but it seems more

Re: [GENERAL] Rsync to a recovering streaming replica?

2017-09-27 Thread Scott Mead
On Wed, Sep 27, 2017 at 1:59 PM, Igor Polishchuk wrote: > Sorry, here are the missing details, if it helps: > Postgres 9.6.5 on CentOS 7.2.1511 > > > On Sep 27, 2017, at 10:56, Igor Polishchuk wrote: > > > > Hello, > > I have a multi-terabyte streaming

Re: [GENERAL] WAL Archive command.

2017-09-27 Thread Scott Mead
On Wed, Sep 27, 2017 at 2:55 PM, Jerry Sievers wrote: > John Britto writes: > > > Hello, > > > > I have a streaming replication setup along with WAL archive. > > > > archive_command = ‘test ! -f /var/pg_archive/%f && cp %p > location>%f && scp %p

Re: [GENERAL] WAL Archive command.

2017-09-27 Thread Jerry Sievers
John Britto writes: > Hello, > > I have a streaming replication setup along with WAL archive. > > archive_command = ‘test ! -f /var/pg_archive/%f && cp %p location>%f && scp %p postgres@192.168.0.123:/%f' > > When the SCP command fails, the master repeatedly tries to send the

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

2017-09-27 Thread Tom Lane
"David G. Johnston" writes: > ​Not sure how much detail you are looking for but the docs say this: > "​Tip: A block containing an EXCEPTION clause is significantly more > expensive to enter and exit than a block without one. Therefore, don't use > EXCEPTION without

Re: [GENERAL] Rsync to a recovering streaming replica?

2017-09-27 Thread Igor Polishchuk
Sorry, here are the missing details, if it helps: Postgres 9.6.5 on CentOS 7.2.1511 > On Sep 27, 2017, at 10:56, Igor Polishchuk wrote: > > Hello, > I have a multi-terabyte streaming replica on a bysy database. When I set it > up, repetative rsyncs take at least 6 hours

[GENERAL] Rsync to a recovering streaming replica?

2017-09-27 Thread Igor Polishchuk
Hello, I have a multi-terabyte streaming replica on a bysy database. When I set it up, repetative rsyncs take at least 6 hours each. So, when I start the replica, it begins streaming, but it is many hours behind right from the start. It is working for hours, and cannot reach a consistent state

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

2017-09-27 Thread David G. Johnston
On Mon, Sep 25, 2017 at 9:13 AM, Denisa Cirstescu < denisa.cirste...@tangoe.com> wrote: > > Can someone please explain to me why this worked? > > What happened behind the scenes? > > I suspect that when you catch exceptions inside of a LOOP and the code > ends up generating an exception, Postgres

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

2017-09-27 Thread Denisa Cirstescu
I am adding an example that is runnable in order to demonstrate my point. CREATE OR REPLACE FUNCTION initialVersion() RETURNS VOID AS $$ declare testDate DATE; begin for i in 1..99 loop begin select now() into strict testDate where 1=0; exception when others then null;

[GENERAL] Logical Replication - test_decoding - unchanged-toast-datum

2017-09-27 Thread Abhinav Singh
Hello, I am currently using PostgreSQL Community version 9.4.9 and then using this instance, I am doing logical replication(using replication slots). I have created the replication slots using the following query: SELECT xlog_position FROM

[GENERAL] Catching errors inside a LOOP is causing performance issues

2017-09-27 Thread Denisa Cirstescu
Hi all, I had a function with a performance issue: totalCharge := 0; FOR myRecord IN ... LOOP .. IF severalConditionsAreMet THEN BEGIN SELECT t1.charge INTO STRICT recordCharge

[GENERAL] WAL Archive command.

2017-09-27 Thread John Britto
Hello, I have a streaming replication setup along with WAL archive. archive_command = ‘test ! -f /var/pg_archive/%f && cp %p %f && scp %p postgres@192.168.0.123:/%f' When the SCP command fails, the master repeatedly tries to send the archived WAL to standby. But during this time, the pg_xlog

Re: [GENERAL] Speed of conversion from int to bigint

2017-09-27 Thread George Neuner
Tomas's suggestion definitely is the better if you're altering the type of a single column. If you need to make more extensive changes to the table structure, copying usually is the better way to go. George -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Speed of conversion from int to bigint

2017-09-27 Thread George Neuner
On Wed, 27 Sep 2017 09:08:25 +0100, Jonathan Moules wrote: >Hi, >(Postgres 9.5 and 9.6) >We have a table of about 650million rows. It's a partitioned table, >with two "child" tables. We want to change its primary key type >from int to bigint while retaining the

Re: [GENERAL] Speed of conversion from int to bigint

2017-09-27 Thread Tomas Vondra
On 09/27/2017 10:08 AM, Jonathan Moules wrote: > Hi, > (Postgres 9.5 and 9.6) > We have a table of about 650million rows. It's a partitioned table, with > two "child" tables. We want to change its primary key type from int to > bigint while retaining the current values. > > We're using this: >

Re: [GENERAL] Final pg_dumpall should happen in Single-User-Mode

2017-09-27 Thread Thomas Güttler
Just for the records, I use this solution now: https://dba.stackexchange.com/questions/186045/how-do-i-prevent-changes-to-my-database/186051#186051 Change the port of PostgreSQL on prod-server-old. This way it is unlikely that clients connect to the DB during pg_dumpall --port=OTHER_PORT

[GENERAL] Speed of conversion from int to bigint

2017-09-27 Thread Jonathan Moules
Hi, (Postgres 9.5 and 9.6) We have a table of about 650million rows. It's a partitioned table, with two "child" tables. We want to change its primary key type from int to bigint while retaining the current values. We're using this: ALTER TABLE dta.my_table ALTER column table_id TYPE bigint;