Re: [GENERAL] Turn off streaming replication - leaving Master running

If i touch my trigger file and promote my secondary to a master - what effect will that have on the Master - will i need to make any changes on that side ? Will it still try and stream data across to the promoted secondary and just fill up the log files with error messages ? On 29 June 2015 at 16:

Re: [GENERAL] Turn off streaming replication - leaving Master running

On Sun, Jun 28, 2015 at 11:34 PM, Andy Erskine wrote: > no snapshot available .. i don't mind running basebackup once i've > finished my test. > > So if someone could help with the steps to turn off replication and bring > secondary up as a standalone db that would be great > thanks. > If people

Re: [GENERAL] Turn off streaming replication - leaving Master running

On 6/28/2015 11:58 PM, Andy Erskine wrote: If i touch my trigger file and promote my secondary to a master - what effect will that have on the Master - will i need to make any changes on that side ? Will it still try and stream data across to the promoted secondary and just fill up the log file

Re: [GENERAL] Turn off streaming replication - leaving Master running

On 6/29/2015 12:06 AM, Jeff Janes wrote: But since you want a clone, what is the point of first setting up streaming, and then breaking it? Just use pg_basebackup to set up a clone directly, without ever having started streaming. It seems like you are just going to confuse yourself about what

Re: [GENERAL] Turn off streaming replication - leaving Master running

Thanks Jeff, I don't want a clone - i want to temporaily turn off replication (and therefore failover) and load a different db into the secondary which is now writable and run some tests. Then i will remove this db and run a basebackup to reinstate a copy of the master and turn on replication agai

Re: [GENERAL] create index on a field of udt

+1 create index on test (((i).id)); ANALYZE explain select * from test where (i).id = 8909; QUERY PLAN - Index Scan using test_id_idx on test (cost=0.43..8.45 rows=1 width=34) Index

Re: [GENERAL] pg_xlog on a hot_stanby slave

On 19/06/2015 03:31, Sameer Kumar wrote: On Thu, 18 Jun 2015 15:17 Xavier 12 > wrote: On 18/06/2015 04:00, Sameer Kumar wrote: On Wed, 17 Jun 2015 15:24 Xavier 12 mailto:mania...@gmail.com>> wrote: On 17/06/2015 03:17, Sameer Kumar wrot

Re: [GENERAL] pg_xlog on a hot_stanby slave

On 16/06/2015 10:55, Xavier 12 wrote: > Hi everyone, > > Questions about pg_xlogs again... > I have two Postgresql 9.1 servers in a master/slave stream replication > (hot_standby). > > Psql01 (master) is backuped with Barman and pg_xlogs is correctly > purged (archive_command is used). > > Hower

[GENERAL] Need for re-index after pg_upgrade

Hi all.   In the man-page for pg_upgrade we see this: pg_upgrade will require a reindex if: * an index is of type hash or GIN   A bit further up we see: All failure, rebuild, and reindex cases will be reported by pg_upgrade if they affect your installation; post-upgrade scripts to rebuild tab

Re: [GENERAL] Inserting from multiple processes?

Hi Dave: On Mon, Jun 29, 2015 at 6:32 AM, Dave Johansen wrote: > The issue is that the following uses 5 XIDs when I would only expect it to > us 1: > BEGIN; > SELECT insert_test_no_dup('2015-01-01', 1, 1); > END; I see. > It appears that the unique violation that is caught and ignored incr

Re: [GENERAL] pg_xlog on a hot_stanby slave

On 29/06/2015 11:38, Stéphane Schildknecht wrote: On 16/06/2015 10:55, Xavier 12 wrote: Hi everyone, Questions about pg_xlogs again... I have two Postgresql 9.1 servers in a master/slave stream replication (hot_standby). Psql01 (master) is backuped with Barman and pg_xlogs is correctly purge

[GENERAL] Which replication is the best for our case ?

Hi guys, We have a PG database with more than 400 GB of data. At this moment, a cron runs each ten minutes and updates about 10 000 lines with complex algorithms in PHP. Each time the cron runs, the website is almost down because some queries have to make an update on the FULL table... Theref

Re: [GENERAL] Which replication is the best for our case ?

On 06/29/2015 06:02 AM, ben.play wrote: Hi guys, We have a PG database with more than 400 GB of data. At this moment, a cron runs each ten minutes and updates about 10 000 lines with complex algorithms in PHP. Each time the cron runs, the website is almost down because some queries have to make

Re: [GENERAL] Which replication is the best for our case ?

On 06/29/2015 06:02 AM, ben.play wrote: Hi guys, We have a PG database with more than 400 GB of data. At this moment, a cron runs each ten minutes and updates about 10 000 lines with complex algorithms in PHP. Each time the cron runs, the website is almost down because some queries have to make

Re: [GENERAL] Need for re-index after pg_upgrade

Andreas Joseph Krogh writes: > In the man-page for pg_upgrade we see this: > pg_upgrade will require a reindex if: > * an index is of type hash or GIN I think that probably refers to some version-specific upgrade situations; I can't see a reason why it would be true in general. Bruce, doesn't th

Re: [GENERAL] Need for re-index after pg_upgrade

På mandag 29. juni 2015 kl. 15:42:22, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh writes: > In the man-page for pg_upgrade we see this: > pg_upgrade will require a reindex if: >  * an index is of type hash or GIN I think that probably refers to some version-specific upgra

Re: [GENERAL] Need for re-index after pg_upgrade

On 06/29/2015 06:42 AM, Tom Lane wrote: Andreas Joseph Krogh writes: In the man-page for pg_upgrade we see this: pg_upgrade will require a reindex if: * an index is of type hash or GIN I think that probably refers to some version-specific upgrade situations; I can't see a reason why it woul

Re: [GENERAL] Correct place for feature requests

On Thu, Jun 25, 2015 at 1:59 PM, Алексей Бережняк wrote: > I think that PostgreSQL is great RDBMS, but one important (for me) > feature that it missing is case-insensitive identifier quotes > ([table].[column]) like in Microsoft SQL Server. > > I know that there are double quotes, but they are cas

Re: [GENERAL] Which replication is the best for our case ?

On Mon, Jun 29, 2015 at 10:02 AM, ben.play wrote: > Hi guys, > > We have a PG database with more than 400 GB of data. > At this moment, a cron runs each ten minutes and updates about 10 000 lines > with complex algorithms in PHP. > > Each time the cron runs, the website is almost down because som

Re: [GENERAL] Which replication is the best for our case ?

Arthur Silva wrote on Monday, June 29, 2015 5:23 PM: > Therefore, I'm asking if it's possible to duplicate my main database on a > slave server in order to run these cron on this second server... then, > replicate these changes on the main database (master). > http://www.postgresql.org/docs/9.3/st

Re: [GENERAL] Re: Get the difference between two timestamp cells but in a special format in PostgreSQL

Check the formatting functions documentation: http://www.postgresql.org/docs/9.4/static/functions-formatting.html # select to_char( now() - '2015-06-27 14:33:24' , 'Y"years" MM"months" DD"days" HH:MI:SS"."MS' ); to_char - 0years 00months 01d

Re: [GENERAL] Which replication is the best for our case ?

On 06/29/2015 08:23 AM, Arthur Silva wrote: On Mon, Jun 29, 2015 at 10:02 AM, ben.play mailto:benjamin.co...@playrion.com>> wrote: Hi guys, We have a PG database with more than 400 GB of data. At this moment, a cron runs each ten minutes and updates about 10 000 lines with c

Re: [GENERAL] Which replication is the best for our case ?

On Mon, Jun 29, 2015 at 1:44 PM, Adrian Klaver wrote: > On 06/29/2015 08:23 AM, Arthur Silva wrote: > >> On Mon, Jun 29, 2015 at 10:02 AM, ben.play > > wrote: >> >> Hi guys, >> >> We have a PG database with more than 400 GB of data. >> At this momen

Re: [GENERAL] Which replication is the best for our case ?

On Mon, Jun 29, 2015 at 6:02 AM, ben.play wrote: > Hi guys, > > We have a PG database with more than 400 GB of data. > At this moment, a cron runs each ten minutes and updates about 10 000 lines > with complex algorithms in PHP. > > Each time the cron runs, the website is almost down because some

Re: [GENERAL] Which replication is the best for our case ?

I think it would help immensely if you provided details such as table_structure, indexes the actual UPDATE query and the reason all rows of the table must be updated. On Mon, Jun 29, 2015 at 1:15 PM, Jeff Janes wrote: > On Mon, Jun 29, 2015 at 6:02 AM, ben.play > wrote: > >> Hi guys, >> >> We h

Re: [GENERAL] Which replication is the best for our case ?

On 6/29/2015 10:41 AM, Melvin Davidson wrote: I think it would help immensely if you provided details such as table_structure, indexes the actual UPDATE query and the reason all rows of the table must be updated. indeed, the whole model of massaging the entire database every 10 minutes is h

[GENERAL] archive_timeout and WAL size

Dear community, I'm using PostgreSQL 9.3.6 on Linux x64. Would sound a stupid questions, and sorry if it was already asked before: if I set the "archive_timeout", and then I have them sent every minute (for example), are the files still 16MB in size, or are they truncated and sent in smaller

Re: [GENERAL] archive_timeout and WAL size

On 06/29/2015 11:49 AM, Edson Richter wrote: Dear community, I'm using PostgreSQL 9.3.6 on Linux x64. Would sound a stupid questions, and sorry if it was already asked before: if I set the "archive_timeout", and then I have them sent every minute (for example), are the files still 16MB in size,

Re: [GENERAL] archive_timeout and WAL size

Thanks, Adrian. That's the reference I was looking for. Atenciosamente, Edson Carlos Ericksson Richter Em 29/06/2015 15:55, Adrian Klaver escreveu: On 06/29/2015 11:49 AM, Edson Richter wrote: Dear community, I'm using PostgreSQL 9.3.6 on Linux x64. Would sound a stupid questions, and sor

[GENERAL] plpgsql question: select into multiple variables ?

Hi, Postgres version 9.3.9 What is wrong with my usage of the plpgsql "select into" concept I have a function to look into a calendar table to find the first and Last weekend date of a month. In this simplified concept function I end up with a NULL for first or last weekend variable. cr

[GENERAL] WAL archive "resend policy"

Dear all, Another question about WAR archiving: what is the "resend policy" if remote storage runs out of space? The failed archives will be resend automatically in future, or there is need for manual interation? Thanks, -- Atenciosamente, Edson Carlos Ericksson Richter -- Sent via pgsql

Re: [GENERAL] WAL archive "resend policy"

On 06/29/2015 12:49 PM, Edson Richter wrote: Dear all, Another question about WAR archiving: what is the "resend policy" if remote storage runs out of space? The failed archives will be resend automatically in future, or there is need for manual interation? They will be resent. Thanks,

Re: [GENERAL] plpgsql question: select into multiple variables ?

On 06/29/2015 12:07 PM, Day, David wrote: Hi, Postgres version 9.3.9 What is wrong with my usage of the plpgsql "select into" concept I have a function to look into a calendar table to find the first and Last weekend date of a month. In this simplified concept function I end up with a NULL

Re: [GENERAL] serialization failure why?

Simon Riggs wrote: > On 17 June 2015 at 13:52, Kevin Grittner wrote: >> Filipe Pina wrote: >>> if drop the foreign key constraint on stuff_ext table there are >>> no failures at all… >> >> It is my recollection that we were excluding the queries used to >> enforce referential integrity constrai

Re: [GENERAL] plpgsql question: select into multiple variables ?

Adrian Klaver writes: > On 06/29/2015 12:07 PM, Day, David wrote: >> What is wrong with my usage of the plpgsql "select into" concept >> I have a function to look into a calendar table to find the first and >> Last weekend date of a month. >> >> create or replace function sys.time_test () >> ret

Re: [GENERAL] plpgsql question: select into multiple variables ?

-Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Monday, June 29, 2015 4:03 PM To: Day, David; pgsql-general@postgresql.org Subject: Re: [GENERAL] plpgsql question: select into multiple variables ? On 06/29/2015 12:07 PM, Day, David wrote: > Hi, > > > > Pos

Re: [GENERAL] plpgsql question: select into multiple variables ?

On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane wrote: > Adrian Klaver writes: > > On 06/29/2015 12:07 PM, Day, David wrote: > >> What is wrong with my usage of the plpgsql "select into" concept > >> I have a function to look into a calendar table to find the first and > >> Last weekend date of a mon

Re: [GENERAL] plpgsql question: select into multiple variables ?

"David G. Johnston" writes: > On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane wrote: >> ... So what you wrote here is equivalent to >> >> SELECT MIN(CAL_DATE),MAX(CAL_DATE) ::date, last_weekend::date INTO >> first_weekend FROM sys.calendar ... > ​Does it help to recognize the fact that "first_week

[GENERAL] Feature request: fsync and commit_delay options per database

Hello all, After reading http://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing I've tried to use commit_delay to make commits really slow on a test environment. Unfortunetely, the maximum value is 100ms (100_000 microseconds). Besides increasing it, it would be great to

Re: [GENERAL] serialization failure why?

On 29 June 2015 at 21:13, Kevin Grittner wrote: > Simon Riggs wrote: > > On 17 June 2015 at 13:52, Kevin Grittner wrote: > >> Filipe Pina wrote: > > >>> if drop the foreign key constraint on stuff_ext table there are > >>> no failures at all… > >> > >> It is my recollection that we were exclud

Re: [GENERAL] Feature request: fsync and commit_delay options per database

=?UTF-8?Q?Br=C3=A1ulio_Bhavamitra?= writes: > Besides increasing it, it would be great to have these two options > (fsync and commit_delay) per database, that is, valid only for > databases configured with them. That would greatly speed up test > running and still make the cluster available for ot

Re: [GENERAL] Feature request: fsync and commit_delay options per database

On Mon, Jun 29, 2015 at 7:43 PM, Tom Lane wrote: > =?UTF-8?Q?Br=C3=A1ulio_Bhavamitra?= writes: >> Besides increasing it, it would be great to have these two options >> (fsync and commit_delay) per database, that is, valid only for >> databases configured with them. That would greatly speed up tes

Re: [GENERAL] Turn off streaming replication - leaving Master running

Hi, On 06/29/2015 09:27 AM, Andy Erskine wrote: Thanks Jeff, I don't want a clone - i want to temporaily turn off replication (and therefore failover) and load a different db into the secondary which is now writable and run some tests. Then i will remove this db and run a basebackup to reinstat

Re: [GENERAL] Turn off streaming replication - leaving Master running

No i don't want a replica. I would like to reconfigure my streaming scenario into two standalone db's i don't want the Master to be effected in anyway and i want it running consistantly .. the secondary i want to reconfigure as a standalone to load a different db and do some testing. When i've fi

Re: [GENERAL] Turn off streaming replication - leaving Master running

On Tue, Jun 30, 2015 at 1:39 PM, Andy Erskine wrote: > No i don't want a replica. > > I would like to reconfigure my streaming scenario into two standalone db's > i don't want the Master to be effected in anyway and i want it running > consistantly .. the secondary i want to reconfigure as a stan

Re: [GENERAL] Turn off streaming replication - leaving Master running

Ok so a question i should have asked at the very beginning .. If i touch my trigger file - promoting the secondary to a master - will that in anyway effect the master thats already running ? IE no files on the master will change ? Then all i'll have to do is shutdown the secondary when i've fini

Re: [GENERAL] Turn off streaming replication - leaving Master running

On Tue, Jun 30, 2015 at 2:29 PM, Andy Erskine wrote: > Ok so a question i should have asked at the very beginning .. > > If i touch my trigger file - promoting the secondary to a master - will > that in anyway effect the master thats already running ? > IE no files on the master will change ? >

Re: [GENERAL] Turn off streaming replication - leaving Master running

agreed there is an element of risk. however a backup of the master will be carried out prior (ok there could potentially be a gap of data during downtime) unfortunately i have nothing else big enough (diskwise) to run my tests on. On 30 June 2015 at 15:47, Michael Paquier wrote: > > > On Tue,

Re: [GENERAL] Turn off streaming replication - leaving Master running

On 6/29/2015 10:55 PM, Andy Erskine wrote: agreed there is an element of risk. however a backup of the master will be carried out prior (ok there could potentially be a gap of data during downtime) unfortunately i have nothing else big enough (diskwise) to run my tests on. rent a virtual s

Re: [GENERAL] Turn off streaming replication - leaving Master running

Cheers all. On 30 June 2015 at 15:58, John R Pierce wrote: > On 6/29/2015 10:55 PM, Andy Erskine wrote: > >> agreed there is an element of risk. >> >> however a backup of the master will be carried out prior (ok there could >> potentially be a gap of data during downtime) >> >> unfortunately i h

Re: [GENERAL] Feature request: fsync and commit_delay options per database

2015-06-29 15:18 GMT-07:00 Bráulio Bhavamitra : > Hello all, > > After reading > http://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing > I've tried to use commit_delay to make commits really slow on a test > environment. Unfortunetely, the maximum value is 100ms (100_000