Re: Autovacuum lock conflict

On Mon, 2019-09-23 at 13:53 -0600, Michael Lewis wrote: > > - For tables that receive only INSERTs, schedule a regular VACUUM > > with "cron" or similar. Unfortunately, PostgreSQL isn't very > > smart > > about vacuuming insert-only tables. > > > What is the need to vacuum on an insert only

Re: How to get timezone offset in timestamp with time zone AT TIME ZONE output.

> "Adrian" == Adrian Klaver writes: Adrian> This has come up before and the general suggestion has been to Adrian> have a column for a naive(timestamp w/o tz) timestamp and a Adrian> column for the timezone. No, that's usually (not always) backwards, and in any event wouldn't solve this p

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 5:28 PM, Corey Taylor wrote: On Mon, Sep 23, 2019 at 7:23 PM Adrian Klaver > wrote: "Once restored, it is wise to run ANALYZE on each restored table so the optimizer has useful statistics; see Section 24.1.3 and Section 24.1.6 for more inf

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 7:23 PM Adrian Klaver wrote: > "Once restored, it is wise to run ANALYZE on each restored table so the > optimizer has useful statistics; see Section 24.1.3 and Section 24.1.6 > for more information." > > So is there some other step in the process that occurs after the res

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 3:56 PM, Corey Taylor wrote: On Mon, Sep 23, 2019 at 5:51 PM Adrian Klaver > wrote: Usually what is seen here is the opposite, that tables are restored and ANALYZE is not run and performance on the subsequent queries is poor due to lac

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 5:51 PM Adrian Klaver wrote: > Usually what is seen here is the opposite, that tables are restored and > ANALYZE is not run and performance on the subsequent queries is poor due > to lack of current statistics. > > What is the restore process? > For these specific legacy

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 3:43 PM, Corey Taylor wrote: On Mon, Sep 23, 2019 at 4:50 PM Adrian Klaver > wrote: Smaller hammer: https://www.postgresql.org/docs/11/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS "autovacuum_enabled, toast.autovacuum_enabled

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 4:50 PM Adrian Klaver wrote: > Smaller hammer: > > > https://www.postgresql.org/docs/11/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS > > "autovacuum_enabled, toast.autovacuum_enabled (boolean)" > > which can be done by ALTER TABLE also: > > https://www.postgresq

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 2:34 PM, Corey Taylor wrote: On Mon, Sep 23, 2019 at 4:31 PM Adrian Klaver > wrote: Hmm, are there triggers on wss_entries that are UPDATEing/DELETEing entries elsewhere? No, that table is pretty much stand-alone.  What we're seeing here is

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 4:31 PM Adrian Klaver wrote: > Hmm, are there triggers on wss_entries that are UPDATEing/DELETEing > entries elsewhere? > No, that table is pretty much stand-alone. What we're seeing here is most likely caused by the initial copy of the legacy db tables. This happens wh

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 1:58 PM, Corey Taylor wrote: On Mon, Sep 23, 2019 at 8:57 AM Tom Lane > wrote: Maybe check for waiting on a lock? It'd be useful to look in pg_stat_activity and/or top(1) while the initial query is running, to see if it seems to be eating CPU or

Re: can't install pg 12 beta on centos 6

On 9/23/19 2:00 PM, Kevin Brannen wrote: Adrian Klaver wrote: On 9/23/19 12:04 PM, Kevin Brannen wrote: I thought I’d get a jump on testing this since we’re a little slow sometimes. ?? I’ve spun up a new VM with Centos 6.10 (the latest). I found https://yum.postgresql.org/testing/12/redhat/rhel

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 9:22 AM Adrian Klaver wrote: > Can we see the actual function/query? > > Also the schema of the table(s) involved? > Of course. This was added to pull some data from legacy db tables into something we could query while converting the process that populates the legacy db.

RE: can't install pg 12 beta on centos 6

> Adrian Klaver wrote: > On 9/23/19 12:04 PM, Kevin Brannen wrote: > > I thought I’d get a jump on testing this since we’re a little slow > > sometimes. ?? > > > > I’ve spun up a new VM with Centos 6.10 (the latest). I found > > https://yum.postgresql.org/testing/12/redhat/rhel-6-x86_64/repoview/ >

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On Mon, Sep 23, 2019 at 8:57 AM Tom Lane wrote: > Maybe check for waiting on a lock? > It'd be useful to look in pg_stat_activity and/or top(1) while the > initial query is running, to see if it seems to be eating CPU or > is blocked on some condition. > I think this will provide information tha

Re: Autovacuum lock conflict

> - For tables that receive only INSERTs, schedule a regular VACUUM > with "cron" or similar. Unfortunately, PostgreSQL isn't very smart > about vacuuming insert-only tables. > What is the need to vacuum on an insert only table? Does that just maintain the freespace map?

Re: can't install pg 12 beta on centos 6

On 9/23/19 12:04 PM, Kevin Brannen wrote: I thought I’d get a jump on testing this since we’re a little slow sometimes. 😊 I’ve spun up a new VM with Centos 6.10 (the latest). I found https://yum.postgresql.org/testing/12/redhat/rhel-6-x86_64/repoview/ to get the packages (BTW, that was kind o

RE: citext, actually probably using extensions

> From: Morris de Oryx > > Not sure about best practices, but what I'm going is like this: > > * Create a schema named extensions. > * Install extensions in this special schema only. I don't put anything else > in there. > * Put the extensions schema early (left) in the search_path for each role.

Re: Error during analyze after upgrade from 10.10 -> 11.4

Ben Snaidero writes: > After upgrade completes when running "vacuumdb -p 5432 -U postgres -a -Z > --analyze-in-stages" I get the following error: > * vacuumdb: processing database "#DBNAME#": Generating minimal optimizer > statistics (1 target) vacuumdb: vacuuming of database "#DBNAME#" failed: >

can't install pg 12 beta on centos 6

I thought I’d get a jump on testing this since we’re a little slow sometimes. 😊 I’ve spun up a new VM with Centos 6.10 (the latest). I found https://yum.postgresql.org/testing/12/redhat/rhel-6-x86_64/repoview/ to get the packages (BTW, that was kind of hard to find). I ended up with: postgresql

Error during analyze after upgrade from 10.10 -> 11.4

Hi, I get the following error after upgrading from Postgres 10.10 -> Postgres 11.4 After upgrade completes when running "vacuumdb -p 5432 -U postgres -a -Z --analyze-in-stages" I get the following error: * vacuumdb: processing database "#DBNAME#": Generating minimal optimizer statistics (1 targ

Re: pg_receivexlog or archive_command

On 2019-09-23 10:25, Vikas Sharma wrote: > I am wondering which one is the best way to archive the xlogs for Backup > and Recovery - pg_receivexlog or archive_command. I recommend using pg_receivexlog. It has two important advantages over archive_command: 1) You can have multiple instances of pg

Re: Autovacuum lock conflict

Christophe Escobar wrote: > We do try to have no maintenance when migrating, thus we generally > use CREATE INDEX CONCURRENTLY to avoid locking a table for reading > and writing. > > We went across an issue with our index creation being locked by an > auto vacuum, as both processes hold a Share Up

Re: unable to drop index because it does not exists

> > Partition key: LIST (date_part('year'::text, mis_ora)) > As an aside, you may benefit from switching to range partitioning* depending on how your queries are written. If you have conditions such as "WHERE mis_ora BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE" or similar, then the fact that your p

Re: Pg_auto_failover

On 9/23/19 10:38 AM, Sonam Sharma wrote: I have installed the PostgreSQL 11 from source.(untarred the file and then make install as postgres user since we don't have root access) . Can you please help in installing the extension for pg_auto_failover.. is it possible as postgres user only ?? F

Re: Pg_auto_failover

I have installed the PostgreSQL 11 from source.(untarred the file and then make install as postgres user since we don't have root access) . Can you please help in installing the extension for pg_auto_failover.. is it possible as postgres user only ?? Thanks, Sonam On Wed, Sep 18, 2019, 8:31 PM Ad

Re: Autovacuum lock conflict

On 9/23/19 8:09 AM, Christophe Escobar wrote: Hi, I am using PostgreSQL 9.6.13. I have some questions about auto vacuums. I find it quite hard to have some info about vacuum locking in the documentation, but from what I found running the command myself is that simple vacuum will hold a Share U

Autovacuum lock conflict

Hi, I am using PostgreSQL 9.6.13. I have some questions about auto vacuums. I find it quite hard to have some info about vacuum locking in the documentation, but from what I found running the command myself is that simple vacuum will hold a Share Update Exclusive lock. We are using auto vacuums

Re: How to get timezone offset in timestamp with time zone AT TIME ZONE output.

On 9/23/19 1:32 AM, Paul McGarry wrote: Hi there, Does anyone have a good way of doing: = select '2020-04-04 15:00:00+00'::timestamp with time zone AT TIME ZONE 'Australia/Sydney';       timezone -  2020-04-05 02:00:00 select '2020-04-04 16:00:00+00'::timestamp with

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

On 9/23/19 12:07 AM, Corey Taylor wrote: First thing I'd look at is whether it's the same execution environment in both cases, eg same search_path. As far as I can tell, it's the same execution environment, same search_path and same user. I found after testing other situations, that

Re: How to represent a bi-directional list in db?

Francisco Olarte writes: >> Could you please elaborate? Suppose I have this table, >> CREATE TABLE stages ( >> id SERIAL PRIMARY KEY, >> name VARCHAR(80) NOT NULL, >> next_id INTEGER REFERENCE stages NULL, >> ); >> What would be the backward query in that case? Forward is clear. This

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

Corey Taylor writes: > I found after testing other situations, that the psql command would always > finish as expected after canceling the first query that ran too long. I > was able to reproduce this scenario with psql and pgadmin4 with various > combinations. Well, that's just weird. It's wel

Re: unable to drop index because it does not exists

On Mon, Sep 23, 2019 at 2:23 PM Ron wrote: > Try specifying the schema name: Yeah, shame on me, I always forget the schema for indexes! Thanks, Luca

Re: unable to drop index because it does not exists

On 9/23/19 6:39 AM, Luca Ferrari wrote: Hi, running 11.5 I've got a partitioned table where I want to destroy an index (on a column that has nothing to do with the partition): respidb=# \d+ respi.root Column|Type | --+-+-

Re: pg_receivexlog or archive_command

Am 23.09.19 um 13:44 schrieb Luca Ferrari: On Mon, Sep 23, 2019 at 10:55 AM Andreas Kretschmer wrote: you can use both of them, and you should consider "Barman". If I remember well Barman uses pg_receivexlog when streaming, and archive_command when doing a "normal" backup. Barman < versio

Re: pg_receivexlog or archive_command

On Mon, Sep 23, 2019 at 10:55 AM Andreas Kretschmer wrote: > you can use both of them, and you should consider "Barman". If I remember well Barman uses pg_receivexlog when streaming, and archive_command when doing a "normal" backup. Also pgbackrest is another good tool for backup. The idea here s

unable to drop index because it does not exists

Hi, running 11.5 I've got a partitioned table where I want to destroy an index (on a column that has nothing to do with the partition): respidb=# \d+ respi.root Column|Type | --+-+- sen_id | integer

Re: How to represent a bi-directional list in db?

Pankaj: On Mon, Sep 23, 2019 at 4:07 AM Pankaj Jangid wrote: > Thanks. This resolved my problem of NULL/NOT NULL conflict. I wasn't > aware that SERIAL is by default NOT NULL. Not only that. Once you strip the annoying NOT NULL the only thing remaining on a serial column is a "default nextval",

Re: pg_receivexlog or archive_command

Am 23.09.19 um 10:25 schrieb Vikas Sharma: Hi, I am wondering which one is the best way to archive the xlogs for Backup and Recovery - pg_receivexlog or archive_command. pg_receivexlog seems best suited because the copied/archived file is streamed as it is being written to in xlog while a

Re: How to get timezone offset in timestamp with time zone AT TIME ZONE output.

> "Paul" == Paul McGarry writes: Paul> Hi there, Paul> Does anyone have a good way of doing: Paul> = Paul> select '2020-04-04 15:00:00+00'::timestamp with time zone AT TIME ZONE Paul> 'Australia/Sydney'; Paul> timezone Paul> - Paul> 2020-04-05 02:00:00

How to get timezone offset in timestamp with time zone AT TIME ZONE output.

Hi there, Does anyone have a good way of doing: = select '2020-04-04 15:00:00+00'::timestamp with time zone AT TIME ZONE 'Australia/Sydney'; timezone - 2020-04-05 02:00:00 select '2020-04-04 16:00:00+00'::timestamp with time zone AT TIME ZONE 'Australia/Sydney';

pg_receivexlog or archive_command

Hi, I am wondering which one is the best way to archive the xlogs for Backup and Recovery - pg_receivexlog or archive_command. pg_receivexlog seems best suited because the copied/archived file is streamed as it is being written to in xlog while archive_command only copies when the WAL is fully wr

Re: postgres 9.6: insert into select finishes only in pgadmin not psql

> > First thing I'd look at is whether it's the same execution environment > in both cases, eg same search_path. > As far as I can tell, it's the same execution environment, same search_path and same user. I found after testing other situations, that the psql command would always finish as expect