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

2017-09-11 Thread Thomas Güttler
I did a stupid mistake. I run the final pg_dumpall without switching to single-user-mode first. With "final" I mean that the dump should be transferred to a different machine. After the dump the old machine was shut down forever. My mistake was that I stopped the services, but one process was

Re: [GENERAL] pg_ident mapping Kerberos Usernames

2017-09-11 Thread rob stone
> > > > Hi Rob, > > How would that work? I was under the impression the first column was > for socket type and limited to > local, host, hostssl, and hostnossl? > > Thunderbird's config has been fixed, so here is the line from > pg_hba.conf line without the > formatting issues: > > host al

[GENERAL] Needing verification on instructions for streaming replication

2017-09-11 Thread Dave Florek
Hi, I'm trying to setup streaming replication on a cluster of postgresql databases and I followed the instructions outlined here ( https://wiki.postgresql.org/wiki/Streaming_Replication) but I'm stuck on the archive and restore commands. In reading the comments from the archive command, WAL archiv

[GENERAL] pg_restore error -- missing pg table

2017-09-11 Thread
We've been doing backups and restores with many tests and uses and never had a problem until now. Sadly, I'm not sure where to even begin looking to figure out what to do, so any pointers would be very much appreciated! The vital stats: Pg: 9.5.1 (this will get upgraded to 10.0 when it's av

Re: [GENERAL] pg_ident mapping Kerberos Usernames

2017-09-11 Thread techmail+pgsql
On 09/11/2017 08:51 AM, rob stone wrote: Hi Rob, How would that work? I was under the impression the first column was for socket type and limited to local, host, hostssl, and hostnossl? Thunderbird's config has been fixed, so here is the line from pg_hba.conf line without the formatting i

Re: [GENERAL] pg_restore error -- missing pg table

2017-09-11 Thread Tom Lane
"" writes: > /opt/pgsql-9.5/bin/pg_restore -v --clean --create --dbname=nms > --schema=public . > ... > pg_restore: [archiver (db)] could not execute query: ERROR: function > _pg_sv_column_array(oid, smallint[]) does not exist > LINE 6: _pg_sv_column_array(k1.conrelid, k1.conkey) AS fk_colu

Re: [GENERAL] Needing verification on instructions for streaming replication

2017-09-11 Thread Jeff Janes
On Mon, Sep 11, 2017 at 7:02 AM, Dave Florek wrote: > Hi, > > I'm trying to setup streaming replication on a cluster of postgresql > databases and I followed the instructions outlined here ( > https://wiki.postgresql.org/wiki/Streaming_Replication) but I'm stuck on > the archive and restore comma

Re: [GENERAL] pg_restore error -- missing pg table

2017-09-11 Thread
--- t...@sss.pgh.pa.us wrote: From: Tom Lane To: "" cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_restore error -- missing pg table Date: Mon, 11 Sep 2017 13:43:16 -0400 "" writes: > /opt/pgsql-9.5/bin/pg_restore -v --clean --create --dbname=nms > --schema=public . > ... > pg_re

Re: [GENERAL] Needing verification on instructions for streaming replication

2017-09-11 Thread Dave Florek
I see. Thanks Jeff, On Mon, Sep 11, 2017 at 2:38 PM, Jeff Janes wrote: > On Mon, Sep 11, 2017 at 7:02 AM, Dave Florek > wrote: > >> Hi, >> >> I'm trying to setup streaming replication on a cluster of postgresql >> databases and I followed the instructions outlined here ( >> https://wiki.postgr

[GENERAL] contrecord is requested

2017-09-11 Thread Scott Marlowe
So we have a db we're trying to rewind and get synced to the master. pg_rewind says it doesn't need rewinding, and when we try to bring it up, it gets this error: "contrecord is requested by 2E7/4028" And fails to get back up. Is this a known issue? Possible bug in the continuation record co

Re: [GENERAL] pg_ident mapping Kerberos Usernames

2017-09-11 Thread Jeff Janes
On Sun, Sep 10, 2017 at 4:31 PM, wrote: > > GSSAPI is the authentication mechanism of choice, and it's working fine. > > Here is what I'm trying to accomplish. > > 'user1' == 'user1' and 'us...@a.domain.tld' == 'user1'. > > From reading the docs, this is done via the pg_ident.conf file, and from

Re: [GENERAL] contrecord is requested

2017-09-11 Thread Michael Paquier
On Tue, Sep 12, 2017 at 5:27 AM, Scott Marlowe wrote: > So we have a db we're trying to rewind and get synced to the master. > pg_rewind says it doesn't need rewinding, and when we try to bring it > up, it gets this error: > > "contrecord is requested by 2E7/4028" > > And fails to get back up.

[GENERAL] Perl script is killed by SIGPIPE

2017-09-11 Thread Yogesh Sharma
> > Dear All, We have one perl script that is opening DB connection and performaing insert DB operation.When duplicate entry Comming in query,Postgres forecfully killing process itself and in Postgres log "unexpected EOF on client connection" error is Comming. This issue is not Comming every time

[GENERAL] Joining 16 tables seems slow

2017-09-11 Thread Frank Millman
Hi all I am using 9.4.4 on Fedora 22. I am experimenting with optimising a SQL statement. One version uses 4 LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE statements. My test involves selec

Re: [GENERAL] Joining 16 tables seems slow

2017-09-11 Thread Pavel Stehule
hi 2017-09-12 8:45 GMT+02:00 Frank Millman : > Hi all > > I am using 9.4.4 on Fedora 22. > > I am experimenting with optimising a SQL statement. One version uses 4 > LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the > filtering into the JOIN section, and I end up with 16 LE