Postgres migration from 9.2 to 14

://www.postgresql.org/message-id/4fd63109744f5ef6eee098fc92c4fa87%40sud0.nz Cheers! Lucas Links: -- [1] https://lpossamai.me/2022-03-09-pg9_to_pg14/

Load Balancer with PostgreSQL

guys use? Also, for context, my DB stack is deployed in AWS on EC2 instances. Thanks! Lucas

Cluster OID Limit

Hello, In the company I work for, some clusters reached the OID limit (2^32) and we had to reinstall the cluster. I was wondering if there is any discussion on: * "compress" the OID space * "warp around" the OID space * segment a OID range for temporary tables with "wrap around" -- Lucas

PostgreSQL replication lag - Suggestions and questions

plication lag, or since we're working on a migration there is no point wasting our time? --- Regards, Lucas > This message is encrypted. Both the Public Key and the GPG encrypted message > are included in this email so that you can verify its origin. publickey - root@sud0.nz - 0xC5E96

Need suggestions about live migration from PG 9.2 to PG 13

am not comfortable with that, using Bucardo, and was hopping you guys could suggest an alternative solution? if anybody has ever done something like this, could share their experiences? BTW - The DB is ~ 1.5TB so pg_upgrade is out of the question, as it takes ages. Thanks in advance! Luc

Re: Need suggestions about live migration from PG 9.2 to PG 13

, but just keep an extra standby node around to fail over > to if everything blows up and you have that covered. I'll test this again, this time in link mode. Lucas publickey - root@sud0.nz - 0xC5E964A1.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature

Re: PostgreSQL 9.2 high replication lag

parameterdetermining how long a standby server should wait before canceling queries that conflict with pending WAL entries received via streaming replication. My question then is: Which queries, if the slave can only receive SELECTs? --- Regards, Lucas > This message is encrypted. Both the

Re: PostgreSQL 9.2 high replication lag

‐‐ Original Message ‐‐‐ On Wednesday, July 14th, 2021 at 5:30 PM, Laurenz Albe laurenz.a...@cybertec.at wrote: On Tue, 2021-07-13 at 20:14 -0700, David G. Johnston wrote: > > On Tue, Jul 13, 2021 at 8:01 PM Lucas r...@sud0.nz wrote: > > > > > According

Re: PostgreSQL 9.2 high replication lag

> Version 9.2 is very old and has a lot of issues related to streaming > replication. Additionally, it is already EOL. Agreed. > Furthermore, max_standby_streaming_delay has no relation with the streaming > lag. It's associated with queries conflicting with data. This parameter will > not spee

Low cache hit ratio

I have tried reducing the shared_buffers parameter from 15GB to 8GB, but the cache hit ratio went down to 60%. Do you guys have any suggestions, on what I could try to get this cache more hits? Thanks in advance! --- Regards, Lucas > This message is encrypted. Both the Public Key and the GPG

Re: PostgreSQL 9.2 high replication lag

canceling standby queries that conflict with about-to-be-applied WAL entries, but this is causing some headache and I was wondering if you guys have other suggestions? What I could do next? Do you think by upgrading PostgreSQL from 9.2 to 13 will somehow improve that? --- Regards, Lucas > T

Re: PostgreSQL 9.2 high replication lag

is too risky to move all customers to RDS at once. They wanted to do it gradually which complicates my life as I would need a bidirectional replication in place. So, I'm still trying to convince them to migrate all customers at once :( Lucas publickey - root@sud0.nz - 0xC5E964A

Re: PostgreSQL 9.2 high replication lag

‐‐‐ Original Message ‐‐‐ On Friday, August 20th, 2021 at 5:29 PM, Laurenz Albe laurenz.a...@cybertec.at wrote: > On Fri, 2021-08-20 at 01:33 +0000, Lucas wrote: > > > After setting max_standby_streaming_delay to 120s it got a lot better. > > > > But the re

Re: ZFS filesystem - supported ?

a hot-standby server only for these snapshots. Lucas

Re: ZFS filesystem - supported ?

On 26/10/2021, at 6:13 AM, Stephen Frost wrote: > > Greetings, > > * Mladen Gogala (gogala.mla...@gmail.com) wrote: >> On 10/23/21 23:12, Lucas wrote: >>> This has proven to work very well for me. I had to restore a few backups >>> already and it always wo

Re: ZFS filesystem - supported ?

> On 27/10/2021, at 8:35 AM, Stephen Frost wrote: > > Greetings, > > * Lucas (r...@sud0.nz) wrote: >> On 26/10/2021, at 6:13 AM, Stephen Frost wrote: >>> * Mladen Gogala (gogala.mla...@gmail.com) wrote: >>>> On 10/23/21 23:12, Lucas wrote: >>>

Re: ZFS filesystem - supported ?

> On 2/11/2021, at 6:00 AM, Stephen Frost wrote: > > Greetings, > > * Lucas (r...@sud0.nz) wrote: >>> On 27/10/2021, at 8:35 AM, Stephen Frost wrote: >>> I do want to again stress that I don't recommend writing your own tools >>> for doing

Re: ZFS filesystem - supported ?

Thanks, > > Stephen Pgbackrest looks awesome. I like that it does it all, it deals with the WAL files, recovery.conf, etc. I’ll definitely have a look at it once we finish the migration to PG 14. Thanks for the suggestion! Lucas

PostgreSQL 14 Slaves setup - Question about WAL Files recovery

13]: [1-1] user=,db=,app=,client= LOG: started streaming WAL from primary at 6FB/DA00 on timeline 1 — Why? Why is PG looking for the wal files in the “wrong” directory? What am I missing here? Thanks in advance. Lucas

Wal files in /pgsql/14/main/pg_wal not removed

= 150 autovacuum_vacuum_scale_factor = 0.02 autovacuum_analyze_scale_factor = 0.005 datestyle = 'iso, mdy' timezone = 'UTC' lc_messages = 'en_US.UTF-8' lc_monetary = 'en_US.UTF-8' lc_numeric = 'en_US.UTF-8' lc_time = 'en_US.UTF-8' default_text_search_config = 'pg_catalog.english' deadlock_timeout = 2s max_files_per_process = 4096 effective_io_concurrency = 200 default_statistics_target = 100 # https://dba.stackexchange.com/a/280727 max_standby_streaming_delay = 30s Thanks! Lucas

Re: Wal files in /pgsql/14/main/pg_wal not removed

> On 27/12/2021, at 5:14 AM, Tom Lane wrote: > > Lucas writes: >> My /pgsql/14/main/pg_wal directory is filling up. The server is not >> respecting the wal_keep_size = 100GB. I have no replication slots in this >> server, and I have already restarted PostgreSQL.

PostgreSQL Management and monitoring tool

ance a similar solution cheaper or even open source? Thanks! Lucas

Re: PostgreSQL Management and monitoring tool

> On 27/01/2022, at 3:00 PM, Bruce Momjian wrote: > > On Thu, Jan 27, 2022 at 02:28:17PM +1300, Lucas wrote: >> Hi guys. >> >> I’m migrating a few databases to PG 14 and was wondering that it would be >> very >> nice to have a tool to help me monitor

Re: PostgreSQL Management and monitoring tool

> On 27/01/2022, at 5:01 PM, Rob Sargent wrote: > > On 1/26/22 20:24, Lucas wrote: >> >>> On 27/01/2022, at 3:00 PM, Bruce Momjian >> <mailto:br...@momjian.us>> wrote: >>> >>> On Thu, Jan 27, 2022 at 02:28:17PM +1300, Lucas wrote: >

PG 14 Create Rule ERROR - RETURNING list has too few entries

Hi, On PG 14.1, when trying to create a RULE [1], I get the following error: ERROR: RETURNING list has too few entries The same syntax works on PG 9.x (previous version of the DB). Any suggestions on a fix, please? Than

Re: PG 14 Create Rule ERROR - RETURNING list has too few entries

> > On 28/02/2022, at 3:44 PM, Lucas wrote: > > Hi, > > On PG 14.1, when trying to create a RULE [1], I get the following error: > Ooops, I forgot to paste the CREATE RULE statement as an example: The idea of these roles is a superimposed version of post-QBO ja_n

Re: PG 14 Create Rule ERROR - RETURNING list has too few entries

> On 28/02/2022, at 4:06 PM, Tom Lane wrote: > > The RETURNING list has to match the column list of the rule's > target table or view (here, doctrine.ja_notes). Maybe you added > some columns to that since this last worked? You are correct. The view has an extra column. Thanks!

PostegreSQL 9.2 to 9.6

tps://bucardo.org/Bucardo/>. Does anybody have any better idea? please share. Just wanna make sure I'm going on the right path. Cluster size is 2TB. Cheers Lucas

Changing wal segment size on existing database cluster

rything seems okay. But corruption might be difficult to detect until it's too late. Thanks, James Lucas

PG 9.2 slave restarted - cache not impacted

Hi guys, I'm a bit confused about PG cache. I have a PostgreSQL 9.2 cluster (yes, we're planning on upgrading it to 12) with a master and a slave database. The application is sending all read requests to the slave, where the master processes the writes. A while ago we had to restart the master

Upgrading 9.2 to 9.6 questions

Hi. We have a setup of 3 Postgres 9.2 nodes (1x master, 2x slaves) running on EC2 instances in AWS. We want to upgrade to 9.6 so we can move to Amazon RDS (it requires at least a 9.3.5 version ). As far as I know, we hav

PostgreSQL Dependency tree

would ask if anybody has any query or tooling that provides the dependency tree ready to go? Thanks in advance! Lucas

Understanding query planner cpu usage

I'm having an perplexing issue in PG 10.1 wherein deleting a large amount of rows from a table causes query planning time to spike dramatically for a while. This happens with or without autovacuums so vacuuming isn't the issue. CPU usage during this time spikes as well. I can't determine if the qu

Re: Understanding query planner cpu usage

On Wed, Feb 21, 2018 at 4:14 PM, Tom Lane wrote: > Lucas Fairchild-Madar writes: > > I'm having an perplexing issue in PG 10.1 wherein deleting a large amount > > of rows from a table causes query planning time to spike dramatically > for a > > while. This happens w

Re: Understanding query planner cpu usage

On Wed, Feb 21, 2018 at 7:28 PM, Tom Lane wrote: > > > What is the planner doing when trying to find the current live max value > of > > the column? > > It's trying to estimate whether a mergejoin will be able to stop short of > reading all the tuples from the other side of the join. (For instanc