Re: Logical decoding on standby

On 12 March 2018 21:18:22 CET, Andreas Joseph Krogh wrote: >Anybody knows if $subject will make it into v11? >  >-- >Andreas Joseph Krogh Why do you think you needs this? Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company

Re: Logical decoding on standby

On 13 March 2018 00:58:27 CET, Andreas Kretschmer wrote: >On 12 March 2018 21:18:22 CET, Andreas Joseph Krogh > wrote: >>Anybody knows if $subject will make it into v11? >>  >>-- >>Andreas Joseph Krogh > >Why do you think you needs this? > >Regards, A

Re: Logical decoding on standby

Am 13.03.2018 um 02:40 schrieb Andres Freund: The subject said logical decoding, not replication. There's a lot of change data capture type workloads where decoding from the standby is quite useful. And the design definitely would work for that, we've explicitly took that into consideration.

Re: Circle and box intersect

Am 16.03.2018 um 11:00 schrieb Martin Moore: PG10 Is there an operator to determine if a box and circle intersect? I can only see box && box and can use centre+rad and distance to calculate circle:circle. Thanks. please don't hijack other mail-threads by answering & changing the subject, y

Re: PostgreSQL 9.6 Temporary files

On 19 March 2018 17:31:20 CET, Jimmy Augustine wrote: >Dear Friends, > >I am newbie to postgresql. >I have 162 GB on my database but when I check size of all tables, I >approximately obtain 80 GB. Indexes? >I also see that I have 68GB of temporary files however I only found Where can you see

Re: PostgreSQL 9.6 Temporary files

On 19 March 2018 18:21:42 CET, Jimmy Augustine wrote: >2018-03-19 18:15 GMT+01:00 Adrian Klaver : > >> On 03/19/2018 10:12 AM, Jimmy Augustine wrote: >> >> >>> On 03/19/2018 09:31 AM, Jimmy Augustine wrote: >>> >>> Dear Friends, >>> >>> I am newbie to

Re: case and accent insensitive

Am 23.03.2018 um 23:04 schrieb MOISES ESPINOSA: I don't know how i could reproduced case insensitive and accent insensitive. Maybe you can use lower() for case insensitive or citext for the same (https://www.postgresql.org/docs/10/static/citext.html) and the unaccent-extension for the accen

Re: pg_dump -Fd -j2 on standby in 9.6.6

On 28 March 2018 07:02:30 CEST, Stefan Petrea wrote: >Hi, > > >I wonder why synchronized snapshots are not supported on standby >servers. > > > If you want to take backups using -Fd on a standby you should pause the reply-process. In this case you don' t need synchronized snapshots. Regards, An

Re: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

Am 28.03.2018 um 09:46 schrieb Stefan Petrea: Hi Andreas, Thank you for your suggestion about pausing/resuming the standby! I've tried running on the standby SELECT pg_xlog_replay_pause() Then running the dump, and it threw the same error I had seen before. run pg_dump with --no-synchronize

Re: Fixed chars

Am 28.03.2018 um 11:11 schrieb Enrico Pirozzi: Hi , I've seen this strange thing. sitedb=# create table test_tb(codice char(7)); CREATE TABLE sitedb=# insert into test_tb values('pippo'); INSERT 0 1 sitedb=# select codice || 'a'::char(1),length(codice) from test_tb  ?column? | length -

Re: pg_basebackup restore a single table

Am 11.04.2018 um 15:53 schrieb camarillo: Can I do a restore of a single table or single base using the archive generated for the basebackup without having to delete the filesystem (/var/lib/pgsql/9.5/*)?. No, but you can use a spare machine to restore the hole database (point-in-time-recove

Re: Doubts about replication..

Am 19.04.2018 um 19:57 schrieb Edmundo Robles: I will use replication as simple backup. please keep in mind, replication is not a backup. All logical errors on the master (delete from table and forgot the where-condition) will replicated to the standby. Andreas -- 2ndQuadrant - The Post

Re: Same condition in the CTE and in the subsequent JOIN using it

Am 25.04.2018 um 17:45 schrieb Alexander Farber: Thank you for any hints, I apologize if my question is too specific and difficult to answer... i haven't checked the whole query, but where-conditions from the outer query are not pushed down into the CTE-query. First the whole CTE will be ma

Re: Recommended way to copy database files on Windows OS (to perform file system level backup)

>An easy way to perform backup of your data is to use PostgreSQL's very >own >pg_dump utility. That's not a file level backup, but a logical backup. Maybe he wants to build standby for streaming replication, for this you needs an other backup. See the answer from Christoph. Andreas -- 2ndQua

Re: binaries for 11 beta compiled with --with-llvm?

On 29 May 2018 13:12:33 CEST, Paul Linehan wrote: >Hi all, > >I have a problem that I just can't seem to solve: > Please create a new thread for a new question. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company

Re: Setting up replication from 9.4 to 10.4

Am 06.06.2018 um 08:16 schrieb Lionel Tressens: Hello, We are running a single PG 9.4 database node we are switching to PG 10.4 (which will run as master + setup of a slave) To minimize downtime when switching from the 9.4 server to the 10.4 one, I would like to setup a replication stream

Re: Performance problem postgresql 9.5

Am 08.06.2018 um 22:09 schrieb Alvaro Herrera: On 2018-Jun-08, Miguel Angel Sanchez Sandoval wrote: Hi guys, migrate from 8.4 to 9.5, all OK except that 2-3 days pass and the database experiences slowness, I execute the linux top command and it shows me a postgres user process executing a st

Re: Catching unique_violation exception on specific column/index

Am 11.06.2018 um 12:58 schrieb Alexey Dokuchaev: What's wrong with: INSERT ... ON CONFLICT (foo_key) DO NOTHING Nothing I guess, except that it is available since 9.5 (right?), and I try to stay compatible with 9.3. Sorry for not saying this in the first place. ./danfe ... 9.3 wil

Re: Merging two database dumps

Am 13.06.2018 um 13:17 schrieb Alex O'Ree: I have a situation with multiple postgres servers running all with the same databases and table structure. I need to periodically export the data from each of there then merge them all into a single server. On  occasion, it's feasible for the same r

Re: Append only replication over intermittent links (with local only delete?)

Am 14.06.2018 um 14:04 schrieb Uri Braun: Hi, I'm looking to run Postgres -- flexible on exact version -- on some devices installed in cars and replicated to a central server over cell phone modems. I expect dropped connections due to: lack of coverage (remote areas), dead spots, tunnels,

Re: question on streaming replication

On 14 June 2018 07:28:53 CEST, Atul Kumar wrote: >Hi, > >I have postgres edb 9.6 version, i have below query to solve it out. > >i have configured streaming replication having master and slave node >on same server just to test it. > >All worked fine but when i made slave service stop, and create

Re: Suggestion about logging only every n-th statement

On 20 June 2018 13:51:25 CEST, Janning Vygen wrote: >Back in 2009 I made a suggestion which is not implemented yet but would > >still be very valuable and easy to implement in my opinion (not for me >as I am not speaking C): > >https://www.postgresql.org/message-id/flat/200903161426.56662.vygen%4

Re: Problem Postgres

Am 26.06.2018 um 10:05 schrieb Emanuele Musella: Good morning, we have the following error: 2018-06-26 09:48:44 CEST ERRORE:  non è stato possibile ottenere informazioni sul file "base/16395/19739338": Permission denied 2018-06-26 09:48:44 CEST ISTRUZIONE:  select p.datname,pg_database_siz

Re: We find few queries running three times simultaneously with same parameters on postgres db

Am 26.06.2018 um 12:19 schrieb amandeep singh: We have been observing our postgres database from past few days,We found few queries running three times simultaneously with same parameters.I would like to back track how a query is running multiple times. they are independend each other. You

Re: We find few queries running three times simultaneously with same parameters on postgres db

On 26 June 2018 12:32:44 CEST, Andreas Kretschmer wrote: > > >Am 26.06.2018 um 12:19 schrieb amandeep singh: >> We have been observing our postgres database from past few days,We >> found few queries running three times simultaneously with same >> parameters.I wou

Re: Split daterange into sub periods

On 05.07.2018 15:49, hmidi slim wrote: Hi, I'm looking for splitting a daterange into many subperiods following this example: Base Date: [2018-01-01, 2018-01-31] overlapped_periods: 1- [ 2018-01-04, 2018-01-06] 2- [ 2018-01-09, 2018-01-12] 3- [ 2018-01-18, 2018-01-19] I try to get such a re

Re: Stored function | Grayed out option when I try modify the return type

Am 12.07.2018 um 10:25 schrieb Abelardo León González: Hello world, Here a newbie in PgAdmin. When I create a stored function and I try to modify it to change the return type (or anything else), I can’t modify it. I am not sure if it is a bug….and I am not sure if this message should be

Re: Stored function | Grayed out option when I try modify the return type

Am 12.07.2018 um 10:56 schrieb Abelardo León González: Thanks Andreas for your response. This is an annoying procedure to modify it. Why did pgAdmin staff decide to do it this way? It's a waste time! No. Two functions with the same name, but different returns types (for instance) are valid

Re: Stored function | Grayed out option when I try modify the return type

Am 12.07.2018 um 11:28 schrieb Abelardo León González: Sorry for top-posting here. :S I won't do it anymore. The idea is to modify the original function in order to meet the requirements. yeah, i understand. It's not to create two functions, it's to modify the original one. Internally,

Re: Database Refresh confusion

Am 13.07.2018 um 13:10 schrieb Rijo Roy: Hello Experts, Today, my colleague asked me if there was any way to check the progress of recovery (backup-recovery) in Postgresql. I told him to check the postgresql logs and look out for keywords such as recovery. He was refreshing the database by

Re: Shared buffers increased but cache hit ratio is still 85%

Am 18.07.2018 um 10:26 schrieb Hans Schou: Am I doing something wrong or should some history be cleared? Reset the stats for that database. You can check the date of last reset with: select stats_reset from pg_stat_database where datname = 'database_name'; and reset it with: ||pg_stat_r

Re: Improving pg_dump performance

Am 23.07.2018 um 09:23 schrieb Ron: Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. you can use the pg_dump from the newer version (9.6) to dump the old database, over the net. In this wa

Re: Improving pg_dump performance

Am 23.07.2018 um 15:06 schrieb Ron: On 07/23/2018 02:32 AM, Andreas Kretschmer wrote: Am 23.07.2018 um 09:23 schrieb Ron: Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. you ca

Re: Connections on cluster not being logged

Am 23.07.2018 um 17:14 schrieb Sandy Becker: I have postgresql 9.4 on a cluster, hardware based.  I need to be able to see which users are connecting to which database and when to be in compliance with our security policies. I have set the following in the postgresql.conf and did a pg_ctl r

Re: Connections on cluster not being logged

Am 23.07.2018 um 17:25 schrieb Sandy Becker: Yes, they are in effect. strange. the logging is working? you can see other and actual entries in the logfile? Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: DB Backup from WAL Slave

On 24 July 2018 14:44:45 CEST, basti wrote: >Hello, > >we have a db master and a slave. - > >How can I do an backup with pg_dumpall from slave? Set hot_standby_feedback to on. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company

Re: Multi client in subscription?

On 30 July 2018 15:29:27 CEST, Adrian Klaver wrote: >On 07/29/2018 11:53 PM, xOChilpili wrote: >> Hi everyone, >> >> I have 2 virtual machines, one that i use at home and the other one >that >> i use at work, so, in the server i have one publication and for each >> client i have the same subscr

Re: WAL-dir filling up with wal_level = logical

On 3 August 2018 12:12:33 CEST, Andreas Joseph Krogh wrote: >Version: PG-10.4 >  >I don't (yet) have any replication-slots configured, and hence no >standbys are >subscribed, but I have wal_level = logical configured to be able to add > >subscribers later. I'm seeing that WAL-dir is filling up wi

Re: Sv: WAL-dir filling up with wal_level = logical

On 3 August 2018 12:33:26 CEST, Andreas Joseph Krogh wrote: >På fredag 03. august 2018 kl. 12:12:33, skrev Andreas Joseph Krogh < >andr...@visena.com >: >Version: PG-10.4 >  >I don't (yet) have any replication-slots configured, and hence no >standbys are >subscribed, bu

Re: How to avoid dead rows in tables.

On 6 August 2018 09:04:45 CEST, Raghavendra Rao J S V wrote: >Hi All, > > >auto vacuum is enabled in our database. But few of the tables contains >the >dead tuples more than 5,000 records. Number of dead rows are keep on >increasing day by day if we didn’t perform the vacuum full. Monthly >once w

Re: Copy over large data Postgresql 9.5

On 16 August 2018 15:41:31 CEST, Vikas Sharma wrote: >Hello Experts, > >I need to transfer pgsql 9.5 data of 90GB from one Cloud provider to >other. We have plenty of downtime to do this. I will be copying over >data >directory after shutting down pgsql services on the source. Why not using stre

Re: CTE with JOIN of two tables is much faster than a regular query

Am 18.08.2018 um 11:36 schrieb kpi6...@gmail.com: What can I do to improve the performance of the regular query without using a CTE? try to rewrite it to a subselect: select ... from ... join (selec ... from ... where ...) x on ... Regards, Andreas -- 2ndQuadrant - The PostgreSQL Suppor

Re: Upgrade/Downgrade

On 23 August 2018 12:51:08 CEST, Sonam Sharma wrote: >Hello, > >My postgres version is 10.4 and I want to downgrade it to 9.5. >and one is at 9.2 and need to upgrade it to 9.5. >Can someone please help how to do this. >Unable to find anything online Why downgrade? Dump and restore should do the j

Re: Which background task looks for pg_xlog in 10?

Am 28.08.2018 um 08:32 schrieb Johann Spies: I see this in /var/log/postgresql/postgresql-10-main.log: postgres postgres@template1 ERROR: could not open directory "pg_xlog": No such file or directory wild guess: some outdated monitoring software. This directory is renamed to pg_wal. Re

Re: WAL replay issue from 9.6.8 to 9.6.10

On 29 August 2018 06:02:45 CEST, Dave Peticolas wrote: >Hello, I'm seeing some issues with WAL replay on a test server running >9.6.10 using WAL archived from a 9.6.8 primary server. It reliably > Can you tell us the wal-level? Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company

Re: WAL replay issue from 9.6.8 to 9.6.10

On 29 August 2018 06:26:06 CEST, Dave Peticolas wrote: >On Tue, Aug 28, 2018 at 9:21 PM Andreas Kretschmer > >wrote: > >> On 29 August 2018 06:02:45 CEST, Dave Peticolas >wrote: >> >Hello, I'm seeing some issues with WAL replay on a test server >running >

Re: Ways to deal with large amount of columns;

Am 30.08.2018 um 11:13 schrieb a: Therefore, the column number would be 1000+. just as a additional note: there is a limit, a table can contains not more than 250-100 columns, dependsing on column types. https://wiki.postgresql.org/wiki/FAQ Regards, Andreas -- 2ndQuadrant - The PostgreSQ

Re: Ways to deal with large amount of columns;

Am 30.08.2018 um 15:15 schrieb Robert Zenz: As David said, you'd be better off having a table that looks like this (in terms of columns): * MONTH * AGENT * CASHFLOW So your query to get the sum of a single agent would be looking like: select sum(CHASFLOW) where

Re: TR: redundant constraint_schema

Am 01.09.2018 um 17:50 schrieb Olivier Leprêtre: I notice that a new constraint "table1_col2_fkeyxxx" is created each time the previous ALTER TABLE ADD COLUMN is called smells like a bug. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: TR: redundant constraint_schema

Am 01.09.2018 um 18:44 schrieb Adrian Klaver: Forgot to include Postgres version, 10.5. also 9.6 and 11beta1 Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: TR: redundant constraint_schema

Am 01.09.2018 um 18:52 schrieb Adrian Klaver: On 09/01/2018 09:47 AM, Olivier Leprêtre wrote: Mine is 9.6 I would submit a bug report here: https://www.postgresql.org/account/login/?next=/account/submitbug/ Olivier, please do that! Regards, Andreas -- 2ndQuadrant - The PostgreSQL Supp

Re: Max number of WAL files in pg_xlog directory for Postgres 9.2 version

Am 05.09.2018 um 19:39 schrieb Raghavendra Rao J S V: Hi All, We are using postgres 9.2 verstion database. 9.2 is out of support. Please consider a upgrade. soon! Please let me know, how many max number of wal files in pg_xlog directory? depends on the workload and on several settings.

Re: Which is the most stable PostgreSQL version yet present for CentOS 7?

Am 19.09.2018 um 16:08 schrieb Raghavendra Rao J S V: Hi All, Which is the most stable PostgreSQL version yet present for CentOS 7? the latest supported minor version. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: *Regarding brin_index on required column of the table

Am 19.09.2018 um 15:51 schrieb Durgamahesh Manne: I have created BRIN index on few columns of the table without any issues. But i am unable to create BRIN index on one column of the table as i got error listed below [local]:6263 postgres@huawei=# CREATE INDEX brin_idx on huawei using brin

Re: *Regarding brin_index on required column of the table

Am 19.09.2018 um 16:43 schrieb Durgamahesh Manne: On Wed, Sep 19, 2018 at 8:02 PM Andreas Kretschmer mailto:andr...@a-kretschmer.de>> wrote: Am 19.09.2018 um 15:51 schrieb Durgamahesh Manne: > I have created BRIN index on few columns of the table without any > i

Re: *Regarding brin_index on required column of the table

Hi, the problem is there: Am 20.09.2018 um 11:48 schrieb Durgamahesh Manne:  Unique  (cost=5871873.64..6580630.73 rows=7400074 width=89) (actual time=326397.551..389515.863 rows=370 loops=1)                                                 | |   ->  Sort  (cost=5871873.64..5973124.65 rows

Re: *Regarding brin_index on required column of the table

Am 20.09.2018 um 13:11 schrieb Durgamahesh Manne: Query was executed at less time without distinct As well as query was taking around 7 minutes to complete execution with distinct  select   distinct  sub_head."vchSubmittersCode" ,rec."vchFileName" , rec."vchCusipFundIDSubFundID" , rec."vc

Re: *Regarding brin_index on required column of the table

Am 21.09.2018 um 17:13 schrieb Durgamahesh Manne: query is below query and plan still not readable. Store it into a textfile and attach it here. Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: *Regarding brin_index on required column of the table

Am 21.09.2018 um 17:49 schrieb Durgamahesh Manne: Please find below attached query plan file query and plan still hard to read :-( Query: SELECT distinct   Max(v."vchSubmittersCode") as vchSubmittersCode   , Max(v."vchRecordType") as  vchRecordType   , Max(v."vchSequenceNumber") as vchSeq

Re: heads up on large text fields.

Am 22.09.2018 um 02:28 schrieb Rob Sargent: However, I get into deep dodo when I try redirecting psql output such as select ld from gt.ld\g /tmp/regen.file works for me if i start psql with -t -A -o /path/to/file (pg 10.5, but psql from 11beta3) Regards, Andreas -- 2ndQuadrant - The

Re: Why my query not using index to sort?

Am 28.09.2018 um 16:49 schrieb Arup Rakshit:  Planning time: 1.867 ms  Execution time: 0.252 ms (6 rows) Why it is showing *6 rows*? Also it seems less than what I had before: the explain-output are 6 rows ;-) Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuad

Re: Why my query not using index to sort?

Am 28.09.2018 um 18:03 schrieb Arup Rakshit: Also I meatn the execution time is less than I had before with enable_sort = off. yeah, but not that much. different plan. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: Price Request MXO-PPQ-101164

Am 01.10.2018 um 20:02 schrieb Pichardo, Ari: Hello team, My name is Ari and I work for SoftwareOne, an international software consulting and reseller company. We have an end user interested in: ·1 2ndQPostgres Platinium For their work environment in Mexico. Do you work with resellers?

Re: regarding bdr extension

Am 02.10.2018 um 15:29 schrieb Adrian Klaver: does this pgdg repository useful for configuration of bdr v3 ? BDR is a third party extension from 2nd Quadrant, they would be the ones to ask about configuring. i said it already, BDR3 is not for public, only for our customers. You will

Re: We are facing "PANIC: could not read from control file:Success error while starting the database.

Am 04.10.2018 um 17:29 schrieb Raghavendra Rao J S V: Hi All, *archive_mode *is turned *on *unfortunately in my Postgres 9.2 database. Due to that disk space is full 100%. We have removed few old xlog files. Now space is available.But still we are facing below problem when we try to start

Re: Slot issues

Am 14.10.2018 um 20:45 schrieb bhargav kamineni: I am getting this ERROR while starting my second slave server PANIC:  too many replication slots active before shutdown HINT:  Increase max_replication_slots and try again. max_replication_slots on my master is 2 and one of them is already ac

Re: [External] Slot issues

Am 14.10.2018 um 21:02 schrieb Vijaykumar Jain: from the doc, it says defaults are 10, that's only valid for version 10, not for 9.x. Default was 0. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: Slot issues

Am 14.10.2018 um 21:07 schrieb bhargav kamineni: Postgres version is 9.5.14 , currently only one slot is there and it is active that's strange. Please check if this setting is active (select from pg_settings). Please check also max_wal_senders. no futher ideas at the moment :-( Regards,

Re: Which index is used in the index scan.

On 17 October 2018 07:39:48 CEST, "Sakai, Teppei" wrote: >Hi > >We have question about index scan. > >We created table and indexes and executed SQL, but the index used by >day is different. >One day the index of c1 was used, and on another day the index of c2 >was used. > - CREATE TABLE tbl1 (c1

Re: Replication question

Am 22.10.2018 um 15:53 schrieb Scot Kreienkamp: I thought I read somewhere that in 9.6, as long as the WAL log is available on disk or in the archive the replication server will provide that to the replication client, and my archive NFS mount didn’t have to be available to all replication cl

Re: BDR: moving a node

Am 25.08.19 um 18:11 schrieb E: What is the process to update the DSN? I assume I'll have to relay the changes in my pg_hba.conf, but do not understand, and don't want to tinker, with BDR before obtaining some educated advice. I apologize if my question comes across as dumb. I understand I

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: 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&qu

Re: Postgres 12: backend crashes when creating non-deterministic collation

Am 04.10.19 um 12:13 schrieb Thomas Kellerer: I was trying to learn how the new non-deterministic collations in v12 work, but the following makes the backend crash: CREATE COLLATION de_ci (provider = icu, locale = 'de-x-icu', deterministic = false); Which leads to: 2019-10-04 11:54:23 CE

Re: GPS coordinates problem

Am 08.10.19 um 12:50 schrieb Timmy Siu: Now, I need Global Position System coordinates as a data type. How do I define it in Postgresql 11 or 12? consider PostGIS. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: Postgres Point in time Recovery (PITR),

On 18 October 2019 07:59:21 CEST, Daulat Ram wrote: >Hello All, >Can you please share some ideas and scenarios how we can do the PITR in >case of disaster. > > >Thanks, Consider Barman. -- 2ndQuadrant - The PostgreSQL Support Company

Re: Can you please suggest how to configure hot_standby_feedback?

Am 31.10.19 um 06:21 schrieb M Tarkeshwar Rao: Can you please suggest how to configure hot_standby_feedback? turn it on if you want execute long running queries on the standby, keep in mind it can lead to more bloat on the master. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Supp

Re: security on user for replication

Am 11.11.19 um 14:26 schrieb PegoraroF10: How can I hide that info from users which are connected to my replica server you can use a .pgpass - file, see the documentation. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: Upgrade PostgreSQL 9.6 to 10.6

Am 09.12.19 um 16:43 schrieb github kran: Hello PostgreSQL Team, I would like to know what would be the best way to do Database migration from PostgreSQL 9.6 engine to 10.6 by creating a new cluster in 10.6 and then copy data. Size of the cluster is 3.8 TB. 1) It would be a new cluster we

Re: Upgrade PostgreSQL 9.6 to 10.6

Am 09.12.19 um 23:37 schrieb github kran: Great, thanks Andreas, So this seems to be a good feature using the core concept of replication. Can I use this extension and do the major upgrade without paying ?. yes, this extension is free. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Sup

Re: Backup and Restore

Am 12.12.19 um 16:12 schrieb Dor Ben Dov: What is the most common used back and restore solution for postgres ? most of our customers are using Barman, which is not a surprise since it is developed by us ;-) Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadra

Re: Backup and Restore

Am 25.12.19 um 14:34 schrieb Dor Ben Dov: Hi All, What Is the best recommended / used tool for backup and restore that you suggest or work with postgres ? depends on your needs, most of our customers using barman. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2n

Re: pg_stat_statements extension

On 13 January 2020 20:15:21 CET, Rushikesh socha wrote: >HI, Is there any good link that shows how to install pg_stat_statements >extension >I am getting below error > >postgres=# CREATE EXTENSION pg_stat_statements; >ERROR: could not open extension control file >"/usr/pgsql-11/share/extension/pg

Re: How to avoid UPDATE on same data in table ?

Am 02.02.20 um 10:24 schrieb Condor: CREATE TRIGGER last_changes   BEFORE UPDATE ON status_table   FOR EACH ROW   WHEN (OLD.* IS DISTINCT FROM NEW.*) try to exclude the column lastchange from the comparison. Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: How to avoid UPDATE on same data in table ?

Am 02.02.20 um 14:37 schrieb Andreas Kretschmer: Am 02.02.20 um 10:24 schrieb Condor: CREATE TRIGGER last_changes   BEFORE UPDATE ON status_table   FOR EACH ROW   WHEN (OLD.* IS DISTINCT FROM NEW.*) try to exclude the column lastchange from the comparison. test=*# select ctid

Re: How to avoid UPDATE on same data in table ?

Am 02.02.20 um 18:18 schrieb Tom Lane: https://www.postgresql.org/docs/current/functions-trigger.html regards, tom lane cool. Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: Backup & Restore

Am 24.02.20 um 09:18 schrieb Dor Ben Dov: Hi All, What is your backup and restore solution in production when working with Postgres ? most of our customers using Barman: https://www.pgbarman.org/ Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: Who mades the inserts?

Am 09.03.20 um 13:52 schrieb Durumdara: Do you know any query which can show me the inserts per databases? And I don't know it works as TPS query? So I need to make differents between measured values in two time point? yes, you can use tup_inserted from pg_stat_database. Regards, Andreas

Re: Real application clustering in postgres.

Am 05.03.20 um 13:07 schrieb Laurenz Albe: There is a closed-source implementation that you can buy: https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndquadrant/ But multi-master replication is complicated to get right, and an applicatoin that uses it has to be specifically designed for

Re: PostgreSQL native multi-master

Am 07.04.20 um 13:39 schrieb Vano Beridze: Hello, What are the plans to support multi-master natively? What solution would you recommend at this point? preferably free. BDR3 works well for our customers, but it isn't free. You can ask us for more information. Regards, Andreas -- 2ndQua

Re: pg11: Query using index, but only for half the partitions

Am 23.04.20 um 10:13 schrieb Stefan Knecht: Seq Scan on snap_20200225 s  (cost=0.00..1.19 rows=1 width=12) the partition is very small, so it's cheaper to scan only the table (one block) than index + table (1 + 1 block). Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company.

Re: pg11: Query using index, but only for half the partitions

Am 23.04.20 um 12:30 schrieb Stefan Knecht: There's no question that this is more expensive than just reading the 95 rows from the index directly and returning them not sure, you can play with enable_seqscan = off and compare the costs. What is the setting for random_page_cost ? Regards,

Re: Clarification related to BDR

Am 14.05.20 um 06:37 schrieb Santhosh Kumar: Can you please help me understand, why the following news is published in "postgresql" with an encouraging message acknowledging BDR as an open source? We invested time and effort to use BDR only to understand at a later point in time, that it is

Re: pg_dump crashes

Am 22.05.20 um 14:37 schrieb Nico De Ranter: Postgres version: 9.5 which minor-version? Can you check if the table has TOAST-Tables? Can you try to select all columns but not TOASTed columns? Maybe there is data-corruption only in toast-tables. Regards, Andreas -- 2ndQuadrant - The Pos

Re: Regarding creation of gin index on column that has varchar datatype

Am 23.05.20 um 12:37 schrieb Durgamahesh Manne: Hi Respected to PGDG GLOBAL TEAM I am getting this error( ERROR:  data type character varying has no default operator class for access method "gin" HINT:  You must specify an operator class for the index or define a default operator class for

Re: [HELP] query regarding replication

Am 09.06.20 um 09:55 schrieb Praveen Kumar K S: Can I achieve master/slave streaming replication by setting WAL_LEVEL to logical on master ? Are there any drawbacks of it ? yes, no problem. the wal's would be a bit larger, that's all. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Suppo

Re: [HELP] query regarding replication

Am 09.06.20 um 10:44 schrieb Praveen Kumar K S: Thanks. Will this approach replicate DDL changes ? sure. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: Postgresql HA Cluster

Am 29.06.20 um 09:33 schrieb Laurenz Albe: That would not provode a multi-master solution, though. There are some commercial solutions for that, but be warned that it would require non-trivial changes to your application. not really with BDR3 ;-) Andreas -- 2ndQuadrant - The PostgreSQL

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

bug in PG13?

Hi all, it seems to me a bug. i have a partitioned table: test=*# select version(); version -  PostgreSQL 13.0 (Ubuntu 13.0-1.pgdg18.04+1) on x86_64-pc-linux-gnu, com

  1   2   3   >