Re: [GENERAL] startup process stuck in recovery

> On Oct 9, 2017, at 17:30, Tom Lane wrote: > > What am I missing to reproduce the problem? Not sure. The actual client behavior here is a bit cryptic (not our code, incompletely logs). They might be creating a savepoint before each temp table creation, without a

Re: [GENERAL] startup process stuck in recovery

> On Oct 9, 2017, at 18:21, Peter Geoghegan wrote: > What's the hot_standy_feedback setting? How about > max_standby_archive_delay/max_standby_streaming_delay? On, 5m, 5m. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list

Re: [GENERAL] startup process stuck in recovery

On Mon, Oct 9, 2017 at 12:08 PM, Christophe Pettus wrote: > Suggestions on further diagnosis? What's the hot_standy_feedback setting? How about max_standby_archive_delay/max_standby_streaming_delay? -- Peter Geoghegan -- Sent via pgsql-general mailing list

Re: [GENERAL] startup process stuck in recovery

Peter Geoghegan writes: > Just a guess, but do you disable autovacuum on your dev machine? (I know I > do.) Nope. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] startup process stuck in recovery

On Mon, Oct 9, 2017 at 5:30 PM, Tom Lane wrote: > and did not see any untoward behavior, at least not till I got to enough > temp tables to overrun the master's shared lock table, and even then it > cleaned up fine. At no point was the standby process consuming anywhere >

Re: [GENERAL] startup process stuck in recovery

Christophe Pettus writes: > The problem indeed appear to be a very large number of subtransactions, each > one creating a temp table, inside a single transaction. It's made worse by > one of those transactions finally getting replayed on the secondary, only to > have

Re: [GENERAL] Equivalence Classes when using IN

On 10 October 2017 at 12:44, Tom Lane wrote: > David Rowley writes: >> If the only reason that is_simple_subquery() rejects subqueries with >> ORDER BY is due to wanting to keep the order by of a view, then >> couldn't we make

Re: [GENERAL] Equivalence Classes when using IN

David Rowley writes: > If the only reason that is_simple_subquery() rejects subqueries with > ORDER BY is due to wanting to keep the order by of a view, then > couldn't we make is_simple_subquery() a bit smarter and have it check > if the subquery is going to be

Re: [GENERAL] startup process stuck in recovery

On Mon, Oct 9, 2017 at 6:12 PM, Christophe Pettus wrote: > >> On Oct 9, 2017, at 14:29, Tom Lane wrote: >> Hmm. Creating or dropping a temp table does take AccessExclusiveLock, >> just as it does for a non-temp table. In principle we'd not have to >>

Re: [GENERAL] startup process stuck in recovery

> On Oct 9, 2017, at 14:29, Tom Lane wrote: > Hmm. Creating or dropping a temp table does take AccessExclusiveLock, > just as it does for a non-temp table. In principle we'd not have to > transmit those locks to standbys, but I doubt that the WAL code has > enough knowledge

Re: [GENERAL] Equivalence Classes when using IN

On 10 October 2017 at 02:51, Tom Lane wrote: > David Rowley writes: >> It's pretty bad practice to have ORDER BY in views. I kinda wish we >> didn't even allow it, but that ship sailed many years ago... > > I think it's actually disallowed by the

Re: [GENERAL] startup process stuck in recovery

Christophe Pettus writes: >> On Oct 9, 2017, at 13:26, Tom Lane wrote: >> My bet is that the source server did something that's provoking O(N^2) >> behavior in the standby server's lock management. It's hard to say >> exactly what, but I'm wondering about

Re: [GENERAL] startup process stuck in recovery

> On Oct 9, 2017, at 13:26, Tom Lane wrote: > My bet is that the source server did something that's provoking O(N^2) > behavior in the standby server's lock management. It's hard to say > exactly what, but I'm wondering about something like a plpgsql function > taking an

Re: [GENERAL] startup process stuck in recovery

> On Oct 9, 2017, at 13:26, Tom Lane wrote: > > Oh, that's really interesting. So it's not *just* releasing locks but > also acquiring them, which says that it is making progress of some sort. It seems to have leveled out now, and is still grinding away. > Can you

Re: [GENERAL] startup process stuck in recovery

Christophe Pettus writes: >> On Oct 9, 2017, at 13:01, Tom Lane wrote: >> Is that number changing at all? > Increasing: > AccessExclusiveLock | 8810 Oh, that's really interesting. So it's not *just* releasing locks but also acquiring them, which says

Re: [GENERAL] startup process stuck in recovery

> On Oct 9, 2017, at 13:01, Tom Lane wrote: > Hmm. Is it possible that the process is replaying the abort of a > transaction with a lot of subtransactions? That's possible, although we're now talking about an hours-long delay at this point. > Is that number changing at

Re: [GENERAL] startup process stuck in recovery

Christophe Pettus writes: > The other observation is that the startup process is holding a *lot* of locks: Hmm. Is it possible that the process is replaying the abort of a transaction with a lot of subtransactions? It seems like maybe you could be getting into an O(N^2)

[GENERAL] Question about memory usage of pg_dump

Hello, I have a question .. okay I know it's a dump but need to ask it because probably I will need to tell of five of my collective bad things... :) My version is PostgreSQL 9.6.5 on x86_64-slackware-linux-gnu, compiled by x86_64-slackware-linux-gcc (GCC) 7.2.0, 64-bit on server IP

Re: [GENERAL] startup process stuck in recovery

On Oct 9, 2017, at 12:18, Christophe Pettus wrote: > > #0 0x558812f4f1da in ?? () > #1 0x558812f4f8cb in StandbyReleaseLockTree () > #2 0x558812d718ee in ?? () > #3 0x558812d75520 in xact_redo () > #4 0x558812d7f713 in StartupXLOG () > #5

Re: [GENERAL] startup process stuck in recovery

> On Oct 9, 2017, at 12:10, Tom Lane wrote: > > Attach to startup process with gdb, and get a stack trace? #0 0x558812f4f1da in ?? () #1 0x558812f4f8cb in StandbyReleaseLockTree () #2 0x558812d718ee in ?? () #3 0x558812d75520 in xact_redo () #4

Re: [GENERAL] startup process stuck in recovery

Christophe Pettus writes: > We're dealing with a 9.5.5 database with the symptom that, after a certain > amount of time after restart, the startup process reaches a certain WAL > segment, and stops. The startup process runs at 100% CPU, with no output > from strace. There

[GENERAL] startup process stuck in recovery

Hi, We're dealing with a 9.5.5 database with the symptom that, after a certain amount of time after restart, the startup process reaches a certain WAL segment, and stops. The startup process runs at 100% CPU, with no output from strace. There are no queries running on the secondary, so it's

Re: [GENERAL] Permissions for Web App

On 10/9/2017 10:51 AM, David G. Johnston wrote: On Mon, Oct 9, 2017 at 9:44 AM, Igal @ Lucee.org >wrote: But I want to give that role permissions on future tables since I add new tables and drop/recreate current ones. ​ALTER DEFAULT PRIVILEGES​

Re: [GENERAL] Using cp to back up a database?

On 10/09/2017 01:02 PM, Scott Mead wrote: On Mon, Oct 9, 2017 at 1:19 PM, Ron Johnson > wrote: Maybe my original question wasn't clear, so I'll try again: is it safe to do a physical using cp (as opposed to rsync)? Yes --

Re: [GENERAL] Using cp to back up a database?

On Mon, Oct 9, 2017 at 1:19 PM, Ron Johnson wrote: > Maybe my original question wasn't clear, so I'll try again: is it safe to > do a physical using cp (as opposed to rsync)? > Yes -- however* you must configure WAL archiving* first. If not, no backup tool, cp, rsync,

Re: [GENERAL] Permissions for Web App

On Mon, Oct 9, 2017 at 9:44 AM, Igal @ Lucee.org wrote: > But I want to give that role permissions on future tables since I add new > tables and drop/recreate current ones. > ​ALTER DEFAULT PRIVILEGES​ ​https://www.postgresql.org/docs/9.6/static/sql-alterdefaultprivileges.html

Re: [GENERAL] pg_start/stop_backup naming conventions

On Mon, Oct 9, 2017 at 9:33 AM, mj0nes wrote: > Hi, > > I'm just starting out on a rolling backup strategy and the naming > convention > has thrown me slightly for the WAL and "backup_label" files. > ​[...]​ > Thanks for any pointers. > ​I'll give out the standard

Re: [GENERAL] Using cp to back up a database?

Maybe my original question wasn't clear, so I'll try again: is it safe to do a physical using cp (as opposed to rsync)? On 10/09/2017 11:49 AM, Darren Douglas wrote: Ron: Here is an explanation that may help a bit. Your script is executing a PHYSICAL backup. A physical backup is simply a

Re: [GENERAL] Using cp to back up a database?

Sorry, forgot about your main question about cp ... So, because the physical backup is a copy of the entire cluster data directory, ANY valid and safe method for copying that directory is ok. In most production installations, that means that an enterprise backup tool may be used to accomplish

Re: [GENERAL] Using cp to back up a database?

On Mon, Oct 9, 2017 at 9:41 AM, Ron Johnson wrote: > On 10/09/2017 11:33 AM, Jeff Janes wrote: > > On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson wrote: > >> Hi, >> >> v8.4.20 >> >> This is what the current backup script uses: >> >> /usr/bin/psql -U

Re: [GENERAL] Using cp to back up a database?

On Mon, Oct 9, 2017 at 7:15 AM, Ron Johnson wrote: > > Sure I want a consistent database. Why doesn't? > > But log shipping requires you to rsync/var/lib/pgsql/data to the remote > server, and that's consistent, so why wouldn't rsync to a local directory > also be

Re: [GENERAL] Using cp to back up a database?

Ron: Here is an explanation that may help a bit. Your script is executing a PHYSICAL backup. A physical backup is simply a full copy of the cluster (instance) data directory ($PGDATA). A physical backup is your best option when you need to backup the cluster data as well as all configuration for

[GENERAL] Permissions for Web App

Hello, I created a role named `webapp` as follows:   CREATE ROLE webapp WITH LOGIN PASSWORD 'changeme'; While in development, I want to give that role permissions on all tables in schema public.  So far I've been using the following command, which works on existing tables:   GRANT ALL

Re: [GENERAL] Using cp to back up a database?

On 10/09/2017 11:33 AM, Jeff Janes wrote: On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson > wrote: Hi, v8.4.20 This is what the current backup script uses: /usr/bin/psql -U postgres -c "SELECT

[GENERAL] pg_start/stop_backup naming conventions

Hi, I'm just starting out on a rolling backup strategy and the naming convention has thrown me slightly for the WAL and "backup_label" files. What I want to do is pair up the backup label files with the associated tar ball of the data directory. When I first ran pg_start_backup('label'), I

Re: [GENERAL] Using cp to back up a database?

On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson wrote: > Hi, > > v8.4.20 > > This is what the current backup script uses: > > /usr/bin/psql -U postgres -c "SELECT pg_start_backup(' > Incrementalbackup',true);" > cp -r /var/lib/pgsql/data/* $dumpdir/data/ > /usr/bin/psql -U

Re: [GENERAL] Using cp to back up a database?

On 09/10/2017 17:13, Michael Paquier wrote: On Mon, Oct 9, 2017 at 11:09 PM, Achilleas Mantzios wrote: In all pg conferences I have been, ppl scream : do not use pg_dump for backups :) It depends on what you are trying to achieve, pg_dump can be fine for

Re: [GENERAL] Using cp to back up a database?

Sure I want a consistent database.  Why doesn't? But log shipping requires you to rsync/var/lib/pgsql/data to the remote server, and that's consistent, so why wouldn't rsync to a local directory also be consistent? On 10/09/2017 08:51 AM, Larry Rosenman wrote: If you want a consistent

Re: [GENERAL] Using cp to back up a database?

On Mon, Oct 9, 2017 at 11:09 PM, Achilleas Mantzios wrote: > On 09/10/2017 16:51, Larry Rosenman wrote: > > If you want a consistent database (you *REALLY* do), pg_dump is the correct > tool. > > In all pg conferences I have been, ppl scream : do not use pg_dump for

Re: [GENERAL] Using cp to back up a database?

On 09/10/2017 16:51, Larry Rosenman wrote: If you want a consistent database (you **REALLY** do), pg_dump is the correct tool. In all pg conferences I have been, ppl scream : do not use pg_dump for backups :) -- Larry Rosenman http://www.lerctr.org/~ler

Re: [GENERAL] Using cp to back up a database?

On Mon, Oct 9, 2017 at 9:51 AM, Larry Rosenman wrote: > If you want a consistent database (you **REALLY** do), pg_dump is the > correct tool. > > > > -- > > Larry Rosenman http://www.lerctr.org/~ler > > Phone: +1 214-642-9640 <(214)%20642-9640>

Re: [GENERAL] Using cp to back up a database?

If you want a consistent database (you *REALLY* do), pg_dump is the correct tool. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 E-Mail: l...@lerctr.org US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106 From:

Re: [GENERAL] Equivalence Classes when using IN

David Rowley writes: > It's not all that clear what your view is doing here. Confusingly > there's a Sort in the plan, yet nothing in the query asked for that, > so I guess that the view must have an ORDER BY. If you get rid of that > the planner would likely use an

[GENERAL] Using cp to back up a database?

Hi, v8.4.20 This is what the current backup script uses: /usr/bin/psql -U postgres -c "SELECT pg_start_backup('Incrementalbackup',true);" cp -r /var/lib/pgsql/data/* $dumpdir/data/ /usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();" Should it use rsync or pg_dump instead?

[GENERAL] Error: "cached plan must not change result type"

Dear Members! At Friday one of our clients got this error: "cached plan must not change result type" He restarted the application and the problem vanished. We used PGDAC to access the database. Firstly we didn't know nothing about this kind of error. But later we realized that

Re: [GENERAL] Equivalence Classes when using IN

On 9 October 2017 at 22:39, Kim Rose Carlsen wrote: > EXPLAIN ANALYZE > SELECT * >FROM customer >JOIN view_customer > ON customer.customer_id = view_customer.customer_id > WHERE age < 20; > >

Re: [GENERAL] Equivalence Classes when using IN

>On 9 October 2017 at 08:01, Kim Rose Carlsen wrote: >> Is this because postgres never consider IN clause when building equivalence >> class's? > >Only btree equality operators are considered at the moment. After good night sleep and reading the previous discussion, I am no

Re: [GENERAL] OR-clause support for indexes

On 8 October 2017 at 21:30, Andreas Joseph Krogh wrote: > There was a while ago a proposed patch for adding $subject; > https://commitfest.postgresql.org/8/454/ That looks like it's been abandoned, but perhaps it's worth asking the author directly? > Is this being worked on?

Re: [GENERAL] Automatically check for anti-patterns in SQL queries

Joy Arulraj schrieb am 08.10.2017 um 13:50: > Hi folks -- We developed a static analysis tool, called SQLCheck, for > automatically identifying anti-patterns in SQL queries. > > https://github.com/jarulraj/sqlcheck > > Our goal is to provide hints to the developers about potential > performance