Re: [GENERAL] Unable to recovery due missing wal_file

On 07/03/2016 06:21 PM, Patrick B wrote: Not sure that would have mattered for the reasons below. You might want to take a look at the below: https://www.postgresql.org/docs/9.5/static/wal.html In particular: https://www.postgresql.org/docs/9.5/static/wal-intro.html

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

I've already switched to 'UPSERT', it didn't resolved deadlock issue by itself... Added LOCK TABLE ... IN EXCLUSIVE MODE; to one session, hope it will help. You did not mention what version of Postgres you are using, if it is 9.5+ you have the 'UPSERT' option available instead of using the

Re: [GENERAL] Unable to recovery due missing wal_file

*slave_new:* server that needs a new copy of the DB *slave01:* streaming replication slave *My steps are:* *1.* ssh slave_new *2.* Stop postgres *3.* rm -rf /var/lib/pgsql/9.2/data/* *4.* ssh postgres@slave01 'pg_basebackup --pgdata=- --format=tar --label=slave_new --progress --host=localhost

Re: [GENERAL] Unable to recovery due missing wal_file

pg_basebackup --pgdata=- --format=tar --label=bkp_server --progress --host=localhost --port=5432 --username=replicator --xlog-method=stream Is that right? Once is finished, just need to restart postgres and set the recovery.conf.restored.command? Cheers

Re: [GENERAL] psql connection option: statement_timeout

On 07/03/2016 07:00 PM, Melvin Davidson wrote: On Sun, Jul 3, 2016 at 7:52 PM, Scott Marlowe > wrote: correction: alter user reporting set statement_timemout=60 is handy for users that should never take a long time to

Re: [GENERAL] Unable to recovery due missing wal_file

> > >> > Not sure that would have mattered for the reasons below. > > You might want to take a look at the below: > > https://www.postgresql.org/docs/9.5/static/wal.html > > In particular: > > https://www.postgresql.org/docs/9.5/static/wal-intro.html > > Short version WAL files are essential to

Re: [GENERAL] psql connection option: statement_timeout

On 07/03/2016 06:51 PM, Scott Marlowe wrote: On Sun, Jul 3, 2016 at 5:15 PM, Melvin Davidson > wrote: On Sun, Jul 3, 2016 at 6:54 PM, Craig Boyd > wrote: Hello All,

Re: [GENERAL] Unable to recovery due missing wal_file

On 07/03/2016 05:36 PM, Patrick B wrote: I don't have it now! But I didn't know that postgres would need that file! If I knew it, I'd have checked just after pg_basebackup started Not sure that would have mattered for the reasons below. You might want to take a look at the below:

Re: [GENERAL] Unable to recovery due missing wal_file

On 07/03/2016 05:23 PM, Patrick B wrote: Yes, I read that! However, I store the wal_files manually into three different servers. I've double checked the files! I got over 500GB of wal_files when the pg_basebackup finished, and still, wasn't enough. Yes, but did you have the 16MB that are

Re: [GENERAL] Unable to recovery due missing wal_file

Yes, I read that! However, I store the wal_files manually into three different servers. I've double checked the files! I got over 500GB of wal_files when the pg_basebackup finished, and still, wasn't enough. I'll re-do the steps but now using the STREAM option.

Re: [GENERAL] Unable to recovery due missing wal_file

On 07/03/2016 03:17 PM, Patrick B wrote: One more question: Could I use pg_basebackup (or another tool like RSYNC) and re-sync the data folder only with the missing data? for example... incremental? So I wouldn't need to copy 2TB again? That assumes the needed WAL files are still on the

Re: [GENERAL] Unable to recovery due missing wal_file

On 07/03/2016 02:48 PM, Patrick B wrote: Hi guys, I'm currently using PostgreSQL 9.2. One of my backup servers went down, and I had to re-sync the all the DB. I used pg_basebackup and, of course, at the same time wal_archive. Steps: 1 - Confirm the wal_files are being copied into the new

Re: [GENERAL] psql connection option: statement_timeout

On Sun, Jul 3, 2016 at 7:52 PM, Scott Marlowe wrote: > correction: > > alter user reporting set statement_timemout=60 is handy for users that > should never take a long time to connect. > > should read > > alter user reporting set statement_timemout=60 is handy for users

Re: [GENERAL] psql connection option: statement_timeout

correction: alter user reporting set statement_timemout=60 is handy for users that should never take a long time to connect. should read alter user reporting set statement_timemout=60 is handy for users that should never take a long time to run a statement. -- Sent via pgsql-general mailing

Re: [GENERAL] psql connection option: statement_timeout

On Sun, Jul 3, 2016 at 5:15 PM, Melvin Davidson wrote: > > > On Sun, Jul 3, 2016 at 6:54 PM, Craig Boyd wrote: > >> Hello All, >> >> I am something of a newbie and I am trying to understand how to pass >> connection options using the psql client. My

Re: [GENERAL] psql connection option: statement_timeout

On 07/03/2016 06:15 PM, Melvin Davidson wrote: On Sun, Jul 3, 2016 at 6:54 PM, Craig Boyd > wrote: Hello All, I am something of a newbie and I am trying to understand how to pass connection options using the psql client. My

Re: [GENERAL] psql connection option: statement_timeout

On Sun, Jul 3, 2016 at 6:54 PM, Craig Boyd wrote: > Hello All, > > I am something of a newbie and I am trying to understand how to pass > connection options using the psql client. My understanding is that it is > possible to do this as part of the psql connection event. >

[GENERAL] psql connection option: statement_timeout

Hello All, I am something of a newbie and I am trying to understand how to pass connection options using the psql client. My understanding is that it is possible to do this as part of the psql connection event. I am on Mint and my PostgreSQL Server version = 9.3.13. I am trying to connect

Re: [GENERAL] Unable to recovery due missing wal_file

One more question: Could I use pg_basebackup (or another tool like RSYNC) and re-sync the data folder only with the missing data? for example... incremental? So I wouldn't need to copy 2TB again?

[GENERAL] Unable to recovery due missing wal_file

Hi guys, I'm currently using PostgreSQL 9.2. One of my backup servers went down, and I had to re-sync the all the DB. I used pg_basebackup and, of course, at the same time wal_archive. Steps: 1 - Confirm the wal_files are being copied into the new server. 2 - Delete /var/lib/pgsql/9.2/data/* 3

Re: [GENERAL] 9.3 to 9.5 upgrade problems

On 07/03/2016 12:41 PM, Andy Colson wrote: On 07/03/2016 11:42 AM, Andy Colson wrote: Now that I think about this more, I think you're on to something. I'm trying to get an _exact_ copy of the master db onto the slave. Checking rsync man, it matches only on size and modified time, and I didn't

Re: [GENERAL] 9.3 to 9.5 upgrade problems

On 07/03/2016 11:42 AM, Andy Colson wrote: Now that I think about this more, I think you're on to something. I'm trying to get an _exact_ copy of the master db onto the slave. Checking rsync man, it matches only on size and modified time, and I didn't include deletes. I'm going to re-try with

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

On 07/02/2016 09:54 AM, trafdev wrote: Hello. I have two transactions (trans1 and trans2) updating tables T1 and T2 in the same order, but in a different way. trans1 creates temp table, copies data from a file and updates tables T1 and T2 from this temp table (using basic UPDATE form). It even

Re: [GENERAL] 9.3 to 9.5 upgrade problems

On 07/03/2016 11:04 AM, Adrian Klaver wrote: On 07/03/2016 08:49 AM, Andy Colson wrote: On 07/03/2016 10:35 AM, Adrian Klaver wrote: On 07/03/2016 08:06 AM, Andy Colson wrote: Hi all, I have a master (web1) and two slaves (web2, webserv), one slave is quite far from the master, the db is 112

Re: [GENERAL] GRANTable Row Permissions

Of course I think of something as soon as I send it. Policies can be granted to a specific role! So create policy xx on table_1 for select to role_1 using (row_id = 1234); Jim On Sun, Jul 3, 2016 at 12:26 PM, James Keener wrote: > I'm trying to work out how to grant

[GENERAL] GRANTable Row Permissions

I'm trying to work out how to grant permissions to rows in a table without having to rebuild the pg auth mechanisms (see below). One option is to have many tables (each representing a row), and grant normally. The other is, like I build below, uses a table and a recursive CTE to resolve the PG

Re: [GENERAL] Stored procedure version control

Mark Morgan Lloyd wrote: Elsewhere, somebody was asking how people implemented version control for stored procedures on (MS) SQL Server. The consensus was that this is probably best managed by using scripts or command files to generate stored procedures etc., but does anybody have any

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

On 07/02/2016 09:01 PM, trafdev wrote: I've also replaced "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" to "INSERT INTO .. ON CONFLICT DO UPDATE ...", but no success - row level deadlocks still occur... Is there a way to tell Postgres to update rows in a specified order? Or

Re: [GENERAL] 9.3 to 9.5 upgrade problems

On 07/03/2016 08:49 AM, Andy Colson wrote: On 07/03/2016 10:35 AM, Adrian Klaver wrote: On 07/03/2016 08:06 AM, Andy Colson wrote: Hi all, I have a master (web1) and two slaves (web2, webserv), one slave is quite far from the master, the db is 112 Gig, so pg_basebackup is my last resort. I

Re: [GENERAL] 9.3 to 9.5 upgrade problems

On 07/03/2016 10:35 AM, Adrian Klaver wrote: On 07/03/2016 08:06 AM, Andy Colson wrote: Hi all, I have a master (web1) and two slaves (web2, webserv), one slave is quite far from the master, the db is 112 Gig, so pg_basebackup is my last resort. I followed the page here:

Re: [GENERAL] 9.3 to 9.5 upgrade problems

On 07/03/2016 08:06 AM, Andy Colson wrote: Hi all, I have a master (web1) and two slaves (web2, webserv), one slave is quite far from the master, the db is 112 Gig, so pg_basebackup is my last resort. I followed the page here: https://www.postgresql.org/docs/9.5/static/pgupgrade.html

Re: [GENERAL] 9.3 to 9.5 upgrade problems

On Sun, Jul 3, 2016 at 11:06 AM, Andy Colson > wrote: Hi all, I have a master (web1) and two slaves (web2, webserv), one slave is quite far from the master, the db is 112 Gig, so pg_basebackup is my last resort. I followed the

Re: [GENERAL] 9.3 to 9.5 upgrade problems

binary replication requires the versions be identical. Also, once you ran pg_upgrade you altered one of the copies so binary replication can no longer work on that either. On Sun, Jul 3, 2016 at 11:06 AM, Andy Colson wrote: > Hi all, > > I have a master (web1) and two

[GENERAL] 9.3 to 9.5 upgrade problems

Hi all, I have a master (web1) and two slaves (web2, webserv), one slave is quite far from the master, the db is 112 Gig, so pg_basebackup is my last resort. I followed the page here: https://www.postgresql.org/docs/9.5/static/pgupgrade.html including the rsync stuff. I practiced it _twice_,