Question related to partitioning with pg_partman

Starting a new thread... Something interesting and not sure if its expected behaviour as below. We are also confused a bit here. In the below example we created two partitioned tables on timestamptz type columns with different time zones and the child partitions are created appropriately with bou

update to 16.2

Hello, We plan to update our customers on SuSE Linux from 11.4, 13.1, 14.1 and 15.1 to 16.2. Do I understand the release notes correct that for this the way is only dump/restore? The release notes say: https://www.postgresql.org/docs/release/16.2/ ... A dump/restore is not required for those ru

Re: update to 16.2

> On Mar 8, 2024, at 00:53, Matthias Apitz wrote: > It does not say definitely that for all other versions a dump/restore is > required. You cannot just replace the binaries to upgrade from an earlier major version to 16.X. The release notes use "a dump/restore (is/is not) required" to indi

Re: update to 16.2

> On 8 Mar 2024, at 09:53, Matthias Apitz wrote: > It does not say definitely that for all other versions a dump/restore is > required. I recommend reading https://www.postgresql.org/docs/16/upgrading.html before attempting anything. Minor version upgrades and major version upgrades are very di

Re: update to 16.2

El día viernes, marzo 08, 2024 a las 12:56:16 -0800, Christophe Pettus escribió: > > > > On Mar 8, 2024, at 00:53, Matthias Apitz wrote: > > It does not say definitely that for all other versions a dump/restore is > > required. > > You cannot just replace the binaries to upgrade from an earlie

Re: update to 16.2

On Fri, Mar 8, 2024 at 5:01 AM Matthias Apitz wrote: > El día viernes, marzo 08, 2024 a las 12:56:16 -0800, Christophe Pettus > escribió: > > > > > > > > On Mar 8, 2024, at 00:53, Matthias Apitz wrote: > > > It does not say definitely that for all other versions a dump/restore > is > > > require

Re: update to 16.2

On Fri, Mar 8, 2024 at 5:01 AM Matthias Apitz wrote: > The other option (pg_upgrade) we never used. > You really should give this a shot. Much easier, and orders of magnitude faster with the --link option. It should work fine even with a custom-compiled postgres (really, as long as pg_dump can s

Windows service randomly stops with no indication why

Running PostgreSQL 15.6 on Windows Server 2022. The database service randomly just stops and the Windows Service auto-restart options don't kick in. The stop is unexpected (not a controlled shut down) because after restarting the service the postgresql log states that the database system was interr

Re: Windows service randomly stops with no indication why

On Fri, Mar 8, 2024 at 9:17 AM Jay Madren wrote: > Running PostgreSQL 15.6 on Windows Server 2022. The database service > randomly just stops and the Windows Service auto-restart options don't kick > in. The stop is unexpected (not a controlled shut down) because after > restarting the service th

Re: Windows service randomly stops with no indication why

Go to "Services", find Postgres, and try a manual restart, see what happens. Then check the recovery tab and see what it is supposed to do on failures - you are probably at the "Do nothing" count limit, hence the no auto restart. If you can manually duplicate the failure to restart, try increasing

Re: Windows service randomly stops with no indication why

On 08/03/2024 14:17, Jay Madren wrote: Running PostgreSQL 15.6 on Windows Server 2022. The database service randomly just stops and the Windows Service auto-restart options don't kick in. The stop is unexpected (not a controlled shut down) because after restarting the service the postgresql log

Re: Windows service randomly stops with no indication why

On 3/8/24 06:17, Jay Madren wrote: Running PostgreSQL 15.6 on Windows Server 2022. The database service randomly just stops and the Windows Service auto-restart options don't kick in. The stop is unexpected (not a controlled shut down) because after restarting the service the postgresql log sta

creating a subset DB efficiently ?

Here's the situation - The DB contains data for several projects. - The tables of the DB contain data for all projects (data is not partitioned on project name or anything like that) - The "project" identifier (table column) exists in a few "parent" tables with many child... grandchild,... tab

Re: v11.5- v15.3 upgrade (linux)

Greetings, On 3/6/24 19:19, David Gauthier wrote: Hi: I'm a PG user in a big corp with an IT dept that administers a PG server/instance that I use.  It's an old install, v11.5, and we need to upgrade to v15.3.  They want to bring the upgraded DB up on a new linux vm which has OS upgrades of i

Re: v11.5- v15.3 upgrade (linux)

Thanks for the reply. When you say "dump/restore" do you mean pg_dump then running the resulting SQL into the destination DB? I like the replication option myself best (min downtime), especially as we use a DB alias for connections. But I don't think I'll be able to sell that to the IT group. Re

Re: update to 16.2

On 3/8/24 00:53, Matthias Apitz wrote: Hello, We plan to update our customers on SuSE Linux from 11.4, 13.1, 14.1 and 15.1 to 16.2. Do I understand the release notes correct that for this the way is only dump/restore? The release notes say: https://www.postgresql.org/docs/release/16.2/ ... . .

Re: creating a subset DB efficiently ?

On Fri, Mar 8, 2024 at 11:22 AM David Gauthier wrote: > Here's the situation > > - The DB contains data for several projects. > - The tables of the DB contain data for all projects (data is not > partitioned on project name or anything like that) > - The "project" identifier (table column) ex

Re: v11.5- v15.3 upgrade (linux)

On 3/8/24 08:57, David Gauthier wrote: Thanks for the reply. When you say "dump/restore" do you mean pg_dump then running the resulting SQL into the destination DB? I like the replication option myself best (min downtime), especially as we use a DB alias for connections.  But I don't think I'l

Re: v11.5- v15.3 upgrade (linux)

On 3/8/24 09:09, Adrian Klaver wrote: On 3/8/24 08:57, David Gauthier wrote: Thanks for the reply. When you say "dump/restore" do you mean pg_dump then running the resulting SQL into the destination DB? I like the replication option myself best (min downtime), especially as we use a DB alias

Re: Windows service randomly stops with no indication why

> What is log_min_messages set to? Increasing it might shed some light. It is not set (commented out). I assume the default is "warning". I will set it to "info" and see if that reveals anything. > Then check the recovery tab and see what it is supposed to do on failures - you are probably at the

Re: v11.5- v15.3 upgrade (linux)

Thanks. On Fri, Mar 8, 2024 at 12:12 PM Adrian Klaver wrote: > On 3/8/24 09:09, Adrian Klaver wrote: > > On 3/8/24 08:57, David Gauthier wrote: > >> Thanks for the reply. > >> > >> When you say "dump/restore" do you mean pg_dump then running the > >> resulting SQL into the destination DB? > >> I

Re: Question related to partitioning with pg_partman

Can somebody help me to understand the behaviour? >

Help diagnosing replication (copy) error

Hi, I'm in the process of migrating a cluster from 15.3 to 16.2. We have a 'zero downtime' requirement so I'm using logical replication to create the new cluster and then perform the switch in the application. I have a situation where all but one table have done their initial copy. The remaining

Re: Help diagnosing replication (copy) error

On 3/8/24 13:50, Steve Baldwin wrote: Hi, I'm in the process of migrating a cluster from 15.3 to 16.2. We have a 'zero downtime' requirement so I'm using logical replication to create the new cluster and then perform the switch in the application. I have a situation where all but one table h

Re: Help diagnosing replication (copy) error

On Sat, Mar 9, 2024 at 8:56 AM Adrian Klaver wrote: > > What are the rest of the values in pg_replication_slots? > > b2bcreditonline=> select * from pg_replication_slots; slot_name | plugin | slot_type | datoid |database | temporary | active | active_p

Re: Question related to partitioning with pg_partman

On 3/8/24 00:23, sud wrote: Starting a new thread... Something interesting and not sure if its expected behaviour as below. We are also confused a bit here. In the below example we created two partitioned tables on timestamptz type columns with different time zones and the child partitions

Re: Help diagnosing replication (copy) error

On 3/8/24 14:04, Steve Baldwin wrote: On Sat, Mar 9, 2024 at 8:56 AM Adrian Klaver > wrote: What are the rest of the values in pg_replication_slots? b2bcreditonline=> select * from pg_replication_slots;                   slot_name                  |  p

Re: Help diagnosing replication (copy) error

On 3/8/24 14:04, Steve Baldwin wrote: On Sat, Mar 9, 2024 at 8:56 AM Adrian Klaver > wrote: What are the rest of the values in pg_replication_slots? b2bcreditonline=> select * from pg_replication_slots;                   slot_name                  |  p

Re: Help diagnosing replication (copy) error

On Sat, Mar 9, 2024 at 9:13 AM Adrian Klaver wrote: > > I should been clearer. > > What are the CREATE PUBLICATION and CREATE SUBSCRIPTION statements? > > The publications were created a while ago. Does this help: b2bcreditonline=> select * from pg_publication; -[ RECORD 1 ]+- oi

Re: Help diagnosing replication (copy) error

On 3/8/24 14:50, Steve Baldwin wrote: Hi, I'm in the process of migrating a cluster from 15.3 to 16.2. We have a 'zero downtime' requirement so I'm using logical replication to create the new cluster and then perform the switch in the application. I have a situation where all but one table

Re: Help diagnosing replication (copy) error

On Sat, Mar 9, 2024 at 11:06 AM Jeff Ross wrote: > > RDS is a black box--who knows what's really going on there? It would be > interesting to see what the response is after you open a support case. > I hope you'll be able to share that with the list. > > This is very mysterious. I logged the cas