Re: [GENERAL] Question about timelines

2015-07-28 Thread Haribabu Kommi
On Wed, Jul 29, 2015 at 3:46 PM, Torsten Förtsch wrote: > Hi, > > we have a complex structure of streaming replication (PG 9.3) like: > > master --> replica1 >| >+-> replica2 --> replica21 >| >+--> replica22 --> replica221 > > Now I want to retire ma

[GENERAL] Question about timelines

2015-07-28 Thread Torsten Förtsch
Hi, we have a complex structure of streaming replication (PG 9.3) like: master --> replica1 | +-> replica2 --> replica21 | +--> replica22 --> replica221 Now I want to retire master and make replica2 the new master: +--> replica1

Re: [GENERAL] [SOLVED] running script on the server

2015-07-28 Thread hamann . w
Adrian Klaver wrote: >> On 07/28/2015 08:28 PM, haman...@t-online.de wrote: >> > >> > Hi, >> > >> > can any of the programming languages (perl, tcl) be used to exec a process >> > on the server >> > and open files? >> >> You are looking for untrusted. >> >> For Python that is the only way it com

Re: [GENERAL] running script on the server

2015-07-28 Thread John McKown
On Tue, Jul 28, 2015 at 10:28 PM, wrote: > > Hi, > > can any of the programming languages (perl, tcl) be used to exec a process > on the server > and open files? > The scenario I have in mind would > - copy some fields of a table to an external text file > - have a script generate binary data fro

Re: [GENERAL] running script on the server

2015-07-28 Thread Adrian Klaver
On 07/28/2015 08:28 PM, haman...@t-online.de wrote: Hi, can any of the programming languages (perl, tcl) be used to exec a process on the server and open files? You are looking for untrusted. For Python that is the only way it comes, hence plpythonu(ntrusted) For Perl see here: http://www

[GENERAL] running script on the server

2015-07-28 Thread hamann . w
Hi, can any of the programming languages (perl, tcl) be used to exec a process on the server and open files? The scenario I have in mind would - copy some fields of a table to an external text file - have a script generate binary data from the text file - load that data into a large object The c

Re: [GENERAL] Postgresql upgrade from 8.4 to latest

2015-07-28 Thread Joshua D. Drake
On 07/28/2015 01:35 PM, AI Rumman wrote: But what I read, in-place upgrade has smaller outage, compared to dump/restore. Correct, in fact if you do it with the link option, it will be very fast. But so many articles on having bugs afterwards. Do you think it is a good idea to use pg_upgrade

Re: [GENERAL] Postgresql upgrade from 8.4 to latest

2015-07-28 Thread Oleksii Kliukin
On Tue, Jul 28, 2015 at 10:35 PM, AI Rumman wrote: > But what I read, in-place upgrade has smaller outage, compared to > dump/restore. Please, keep in mind that while the direct outage (when the database is shut down) is small, there will be a time period right after the upgrade when PostgreSQL w

Re: [GENERAL] Postgresql upgrade from 8.4 to latest

2015-07-28 Thread John R Pierce
On 7/28/2015 1:35 PM, AI Rumman wrote: But what I read, in-place upgrade has smaller outage, compared to dump/restore. But so many articles on having bugs afterwards. Do you think it is a good idea to use pg_upgrade for critical database application? most of those are application issues with 9

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

2015-07-28 Thread Heikki Linnakangas
A-ha, I succeeded to reproduce this now on my laptop, with pgbench! It seems to be important to have a very large number of connections: pgbench -n -c400 -j4 -T600 -P5 That got stuck after a few minutes. I'm using commit_delay=100. Now that I have something to work with, I'll investigate this

Re: [GENERAL] Postgresql upgrade from 8.4 to latest

2015-07-28 Thread John R Pierce
On 7/28/2015 1:29 PM, Joshua D. Drake wrote: I need to upgrade Postgresql database from 8.4 to latest stable version (9.4). The db size is almost 2.5 TB. Is pg_upgrade in-place is a good idea for it? With quite a bit of testing, yes. yeah, that was my thought to... clone the database to a

Re: [GENERAL] Postgresql upgrade from 8.4 to latest

2015-07-28 Thread AI Rumman
But what I read, in-place upgrade has smaller outage, compared to dump/restore. But so many articles on having bugs afterwards. Do you think it is a good idea to use pg_upgrade for critical database application? Or any other tool should I consider? For example - slony? Thanks for advice. Regards.

Re: [GENERAL] Postgresql upgrade from 8.4 to latest

2015-07-28 Thread Joshua D. Drake
On 07/28/2015 01:12 PM, AI Rumman wrote: Hi, I need to upgrade Postgresql database from 8.4 to latest stable version (9.4). The db size is almost 2.5 TB. Is pg_upgrade in-place is a good idea for it? With quite a bit of testing, yes. But keep in mind, it is still an outage. JD Thanks fo

[GENERAL] Logical decoding off of a replica?

2015-07-28 Thread Curt Micol
Hello, I've been working to get a replica setup to perform logical decoding and haven't been able to get the right configuration. I've tried everything I can think of. While researching I found this post on Stack Overflow: http://stackoverflow.com/questions/30351628/logical-decoding-on-a-standby-n

[GENERAL] Postgresql upgrade from 8.4 to latest

2015-07-28 Thread AI Rumman
Hi, I need to upgrade Postgresql database from 8.4 to latest stable version (9.4). The db size is almost 2.5 TB. Is pg_upgrade in-place is a good idea for it? Thanks for advice. Regards.

[GENERAL] Logical decoding off of a replica?

2015-07-28 Thread Curt Micol
Hello, I've been working to get a replica setup to perform logical decoding and haven't been able to get the right configuration. I've tried everything I can think of. While researching I found this post on Stack Overflow: http://stackoverflow.com/questions/30351628/logical-decoding-on-a-standby-n

Re: [GENERAL] How to speed up pg_trgm / gin index scan

2015-07-28 Thread Arthur Silva
On Tue, Jul 28, 2015 at 10:34 AM, Christian Ramseyer wrote: > > > On 22/06/15 13:51, Christian Ramseyer wrote: > > Hi > > > > I have a pretty large table with syslog messages. > > > > It is already partitioned by month, and for a single month I have e.g. > > > > > > DM=# \d+ logs_01 > > > > C

Re: [GENERAL] How to speed up pg_trgm / gin index scan

2015-07-28 Thread Merlin Moncure
On Tue, Jul 28, 2015 at 8:34 AM, Christian Ramseyer wrote: > On 22/06/15 13:51, Christian Ramseyer wrote: >> Hi >> >> I have a pretty large table with syslog messages. >> >> It is already partitioned by month, and for a single month I have e.g. >> >> >> DM=# \d+ logs_01 >> >> Column|

Re: [GENERAL] How to speed up pg_trgm / gin index scan

2015-07-28 Thread Christian Ramseyer
On 22/06/15 13:51, Christian Ramseyer wrote: > Hi > > I have a pretty large table with syslog messages. > > It is already partitioned by month, and for a single month I have e.g. > > > DM=# \d+ logs_01 > > Column|Type

Re: [GENERAL] Error: "When checking a pre-PG 9.1 live old server, you must specify the old server's port number."

2015-07-28 Thread Adrian Klaver
On 07/28/2015 06:12 AM, Furlong, Shane wrote: I am trying to upgrade my PostgreSQL instance from 9.0.22 to 9.4.4. However, I am getting the following error when executing pg_upgrade. “When checking a pre-PG 9.1 live old server, you must specify the old server's port number. Failure, exiting”

[GENERAL] Error: "When checking a pre-PG 9.1 live old server, you must specify the old server's port number."

2015-07-28 Thread Furlong, Shane
I am trying to upgrade my PostgreSQL instance from 9.0.22 to 9.4.4. However, I am getting the following error when executing pg_upgrade. "When checking a pre-PG 9.1 live old server, you must specify the old server's port number. Failure, exiting" I am executing the following command. I have tr

Re: [GENERAL] postgresql-ctl systemd failed: permission denied

2015-07-28 Thread arnaud gaboury
On Tue, Jul 28, 2015 at 1:43 PM, CaT wrote: > On Tue, Jul 28, 2015 at 01:31:29PM +0200, arnaud gaboury wrote: > > On Tue, Jul 28, 2015 at 1:26 PM, Francisco Olarte < > fola...@peoplecall.com> > > wrote: > > > > > Hi Arnaud... > > > > > > On Tue, Jul 28, 2015 at 12:13 PM, arnaud gaboury > > > wro

Re: [GENERAL] postgresql-ctl systemd failed: permission denied

2015-07-28 Thread arnaud gaboury
On Tue, Jul 28, 2015 at 1:37 PM, arnaud gaboury wrote: > > > On Tue, Jul 28, 2015 at 1:31 PM, Albe Laurenz > wrote: > >> arnaud gaboury wrote: >> > On Fedora 22 >> > >> > % pg_ctl -V >> > pg_ctl (PostgreSQL) 9.4.4 >> > >> > >> > %

Re: [GENERAL] postgresql-ctl systemd failed: permission denied

2015-07-28 Thread CaT
On Tue, Jul 28, 2015 at 01:31:29PM +0200, arnaud gaboury wrote: > On Tue, Jul 28, 2015 at 1:26 PM, Francisco Olarte > wrote: > > > Hi Arnaud... > > > > On Tue, Jul 28, 2015 at 12:13 PM, arnaud gaboury > > wrote: > > > On Fedora 22 > > ... > > > Jul 28 11:49:57 poppy postgresql-ctl[307]: FATAL:

Re: [GENERAL] postgresql-ctl systemd failed: permission denied

2015-07-28 Thread arnaud gaboury
On Tue, Jul 28, 2015 at 1:31 PM, Albe Laurenz wrote: > arnaud gaboury wrote: > > On Fedora 22 > > > > % pg_ctl -V > > pg_ctl (PostgreSQL) 9.4.4 > > > > > > % systemctl status postgresql.service -l > > ● postgresql.service - PostgreS

Re: [GENERAL] postgresql-ctl systemd failed: permission denied

2015-07-28 Thread Albe Laurenz
arnaud gaboury wrote: > On Fedora 22 > > % pg_ctl -V > pg_ctl (PostgreSQL) 9.4.4 > > > % systemctl status postgresql.service -l > ● postgresql.service - PostgreSQL database server >Loaded: loaded (/etc/systemd/system/postgresql.

Re: [GENERAL] postgresql-ctl systemd failed: permission denied

2015-07-28 Thread arnaud gaboury
On Tue, Jul 28, 2015 at 1:26 PM, Francisco Olarte wrote: > Hi Arnaud... > > On Tue, Jul 28, 2015 at 12:13 PM, arnaud gaboury > wrote: > > On Fedora 22 > ... > > Jul 28 11:49:57 poppy postgresql-ctl[307]: FATAL: 42501: could not > > open log file "/storage/log/postgresql/postgresql-Tue.log": Per

Re: [GENERAL] postgresql-ctl systemd failed: permission denied

2015-07-28 Thread Francisco Olarte
Hi Arnaud... On Tue, Jul 28, 2015 at 12:13 PM, arnaud gaboury wrote: > On Fedora 22 ... > Jul 28 11:49:57 poppy postgresql-ctl[307]: FATAL: 42501: could not > open log file "/storage/log/postgresql/postgresql-Tue.log": Permission > denied > Jul 28 11:49:57 poppy postgresql-ctl[307]: LOCATION: l

[GENERAL] postgresql-ctl systemd failed: permission denied

2015-07-28 Thread arnaud gaboury
On Fedora 22 % pg_ctl -V pg_ctl (PostgreSQL) 9.4.4 % systemctl status postgresql.service -l ● postgresql.service - PostgreSQL database server Loaded: loaded (/etc/systemd/system/postgresql.service; enabled; vendor preset: disable

Re: *SOLVED* [GENERAL] Connections closing due to "terminating connection due to administrator command"

2015-07-28 Thread Herouth Maoz
> On 27 ביולי 2015, at 18:20, Tom Lane wrote: > > Herouth Maoz writes: >> So I’m left with the question of what caused the shutdown on July 21st. > > Well, you had > > 2015-07-21 15:37:59 IDT LOG: received fast shutdown request > > There is exactly one place in the Postgres code that prints

Re: [GENERAL] Lots of stuck queries after upgrade to 9.4

2015-07-28 Thread Spiros Ioannou
> > Hmm. With that CTE query, were there other queries running at the same > time? yes, a multitude of INSERT and one COMMIT as I remember. I've noticed that in every stuck situation there was always 1 stuck COMMIT running. We do mainly INSERT and SELECT, very rarely UPDATE or DELETE. but the stu