Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp

Deepika S Gowda writes: > Hi, > > On postgres 11.7 Master/Slave node, there is column named "createddate" > with datatype "timestamp without time zone" with default value as "now()"; > > Column Name | Date Type | Default value > createddate |timestamp without time zone|Now() > >

Re: Safe switchover

Hi Stephen, > On 10. Jul, 2020, at 17:45, Stephen Frost wrote: > > Sure, if you know exactly why the former primary failed and have > confidence that nothing actually bad happened then pg_rewind can work > (though it's still not what I'd generally recommend). > > If you don't actually know what

Re: Postgresql-12 taking more time to execute the query

On Sat, 11 Jul 2020 at 02:48, Vishwa Kalyankar wrote: > Below is the output of the query explain and analyze result. You may find better help here if you follow the advice given in https://wiki.postgresql.org/wiki/Slow_Query_Questions David

Re: Postgresql-12 taking more time to execute the query

On 7/10/20 7:48 AM, Vishwa Kalyankar wrote: Hi,   Below is the output of the query explain and analyze result.    Note : port 5434 is postgresql12  and 5433 is postgresql10 Well that is not enlightening. What's happening inside the function is not being shown. You might have to ask this o

Re: PG Admin 4

I did the same for at least a year, but I must admit that v4 has improved greatly since its initial release. Also it turns out is handy for running in a docker-compose environment so no matter who is starting up on your team, they always have a database and a database admin tool at the ready along

Re: PG Admin 4

I gave up on PGAdmin4 and went back to PGAdmin3, although 3 is deprecated now. The History tab will show you what you want after executing a SQL statement. I don't use Windows any more than I have to but when I did try PGAdmin4 on windows, the feedback was sometimes there and sometimes not. Li

Re: PG Admin 4

On 7/10/20 12:54 PM, Robert West wrote: Yes...all that is in there is the same results for the entire batch..no intermediate results for each piece of DDL. Hmm, that is something you might have to ask here: https://www.pgadmin.org/support/list/ > I'm submitting a large series of DDL chan

Re: PG Admin 4

Why would you shun the ease of command line batch control? Tim Clarke MBCS IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 10/07/2020 17:36, rwest wrote: Oh sorry, should have specified that. We're running on a Windows platform. We're trying to avoid running anything com

How to do phrase search?

Hello, I have the following table: so2, which has following column details: ​ id, title, posts, body (tsvector). And I created the index on the following: "so2_pkey" PRIMARY KEY, btree (id) "body" gin (body) ​ And I wanted to query on my tsvector with the string: `Is it possible to toggle

Re: PG Admin 4

Oh sorry, should have specified that. We're running on a Windows platform. We're trying to avoid running anything command-line when doing DDL releases and leverage whatever PG Admin 4 can provide us. I'm just wondering why we don't see the results of each CREATE or ALTER statement as the script

RE: PG Admin 4

Yes...all that is in there is the same results for the entire batch..no intermediate results for each piece of DDL. I'm submitting a large series of DDL changes in one batchI'm still looking how to see the results of the execution of each DDL statement. RW -Original Message- Fr

Re: PG Admin 4

Ah, sorry that i didn't specify. I'm running on a Windows platform. We're trying to avoid running in a command-line mode if possible, and leverage the options PG Admin 4 has available to us. Just find it odd that it doesn't report out each Create or Alter statement as it executes the script.

Re: PG Admin 4

On 7/10/20 8:53 AM, rwest wrote: I'm relatively new to PostgreSql and am trying to navigate my way around the tools like PG Admin 4 to do Database Admin work. I'm trying to run an entire set of DDL with lots of tables, indexes, etc. through PG Admin 4 for a database. The only thing I saw after

Re: PG Admin 4

On 7/10/20 9:53 AM, rwest wrote: I'm relatively new to PostgreSql and am trying to navigate my way around the tools like PG Admin 4 to do Database Admin work. I'm trying to run an entire set of DDL with lots of tables, indexes, etc. through PG Admin 4 for a database. The only thing I saw aft

PG Admin 4

I'm relatively new to PostgreSql and am trying to navigate my way around the tools like PG Admin 4 to do Database Admin work. I'm trying to run an entire set of DDL with lots of tables, indexes, etc. through PG Admin 4 for a database. The only thing I saw after I ran the script was a message abou

Re: Safe switchover

Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > > On 10. Jul, 2020, at 17:29, Stephen Frost wrote: > > Patroni also has the option to use pgbackrest instead of pg_rewind. > > right. Sorry, I forgot about that. We use pg_rewind which works great. Sure, if you know exactly why the fo

Re: Safe switchover

Hi Stephen, > On 10. Jul, 2020, at 17:29, Stephen Frost wrote: > > Patroni also has the option to use pgbackrest instead of pg_rewind. right. Sorry, I forgot about that. We use pg_rewind which works great. Cheers, Paul

Re: Safe switchover

Greetings, * Paul Förster (paul.foers...@gmail.com) wrote: > I wouldn't work out the procedure myself, especially since there is a free > working solution already. It's dangerous if you do it yourself and make a > mistake. > > In our company, we rely on Patroni (https://github.com/zalando/patro

Re: invalid non-zero objectSubId for object class

On 7/10/20 8:06 AM, Michel Pelletier wrote: On Fri, Jul 10, 2020 at 5:37 AM Alvaro Herrera > wrote: On 2020-Jul-09, Michel Pelletier wrote: > I restored a snapshot and I can drop the tables there, so we'll likely > proceed  to swap the replicas

Re: invalid non-zero objectSubId for object class

On Fri, Jul 10, 2020 at 5:37 AM Alvaro Herrera wrote: > On 2020-Jul-09, Michel Pelletier wrote: > > > I restored a snapshot and I can drop the tables there, so we'll likely > > proceed to swap the replicas over tomorrow. I have this corrupted > > instance i can continue to debug on if necessary

Re: Postgresql-12 taking more time to execute the query

Hi, Below is the output of the query explain and analyze result. Note : port 5434 is postgresql12 and 5433 is postgresql10 -bash-4.2$ psql -p 5434 psql (12.3) Type "help" for help. postgres=# \c IPDS_KSEB You are now connected to database "IPDS_KSEB" as user "postgres". IPDS_KSEB=# explai

Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp

On 7/10/20 7:03 AM, Deepika S Gowda wrote: Hi, On postgres 11.7 Master/Slave node, there is column named "createddate" with datatype "timestamp without time zone" with default value as "now()"; Column Name | Date Type                 | Default value createddate |timestamp without time zone|No

Issue with timestamp without time zone datatype with default value as now()/localtimestamp

Hi, On postgres 11.7 Master/Slave node, there is column named "createddate" with datatype "timestamp without time zone" with default value as "now()"; Column Name | Date Type | Default value createddate |timestamp without time zone|Now() Issue: From the java application , data i

Re: Postgresql-12 taking more time to execute the query

On 7/9/20 11:50 PM, Vishwa Kalyankar wrote: Hi Team,    I Need help or any suggestion on below mentioned issue. Previously we are running postgresql-10with postgis 2.5.3 and now we are updated to postgresql-12 and postgis-3.0.1, and in postgresql-10 one query is taking 20 sec and same query

Re: Wal_keep_segment value too high

Hi Andreas, Actually there will be sync gap almost 3-4hours during the ETL jobs between primary and streaming replication db ,so I just want to make sure is there any affect in replication bcoz of high value of wal_keep_segments . Second thing we have implemented pgbackrest in archive_command so

Re: invalid non-zero objectSubId for object class

On 2020-Jul-09, Michel Pelletier wrote: > I restored a snapshot and I can drop the tables there, so we'll likely > proceed to swap the replicas over tomorrow. I have this corrupted > instance i can continue to debug on if necessary. There seem to be some > other issues now that we're investigat

Re: Wal_keep_segment value too high

On 10 July 2020 10:26:25 CEST, Brajendra Pratap Singh wrote: >Hi, > >What will happen if the wal_keep_segments value is too high ,is this wasted disk space. What do you want to achive? Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company

Wal_keep_segment value too high

Hi, What will happen if the wal_keep_segments value is too high ,is this affect the database performance anyhow like checkpoint and all or is there because of this any delay in the replication sync or wal records transfer from primary to replication postgresql db? Thanks, Brajendra

Re: Postgresql-12 taking more time to execute the query

Vishwa Kalyankar schrieb am 10.07.2020 um 08:50: > Previously we are running postgresql-10with postgis 2.5.3 and now we > are updated to postgresql-12 and postgis-3.0.1, and in postgresql-10 > one query is taking 20 sec and same query is taking upto 80 sec. Most of the slowdowns I have seen when u

Re: Postgresql Backup Encryption

Hi Singh, > On 10. Jul, 2020, at 07:29, Brajendra Pratap Singh > wrote: > > Hi, > > Please let me know the way to encrypt/decrypt the postgresql backup uaing > pg_dump and pgbackrest. > > Also let me know the other good possibility if any. > > Thanks, > Singh don't use the -f option with p

Re: Safe switchover

Hi James, I wouldn't work out the procedure myself, especially since there is a free working solution already. It's dangerous if you do it yourself and make a mistake. In our company, we rely on Patroni (https://github.com/zalando/patroni). Yes, it uses pg_rewind in the background but it does

Postgresql Backup Encryption

Hi, Please let me know the way to encrypt/decrypt the postgresql backup uaing pg_dump and pgbackrest. Also let me know the other good possibility if any. Thanks, Singh

Re: Safe switchover

> - open connection to database > - smart shutdown master > - terminate all other connections > - wait for shutdown (archiving will finish) > OK despite what it looked like from the code - upon testing it seems like even a fast shutdown will wait for logs to be archived *as long as progress is bei