Re: Upgrading PG11 to PG17 without dump/restore
On Thu, May 1, 2025 at 9:06 AM Durumdara wrote: > Hello! > > There is a heavily used server, with older debian, and PG11. > The data is more than 1,2 TB. > The PG_Upgrade is not possible because of lesser space and too old debian. > > As we see now we have only one way to move this server. > 1.) Installing a new server with actual debian. > 2.) Installing the newest PG (17) on it. > 3.) Stop work on one database. Dump it in the old, restore it in the new > and start the work with that. So we can move them one by one. > > But this seems to be very hard, because we need to do this through an > internet connection, and the data is too much. > The new server isn't in the same data center? > I have a question about it - is there a better way to do this? > > For example we make a new cluster element (a read only slave) with newest > debian/PG, and use it to move the data in the background (replication). > And then we rename it to master. But I don't know if it's possible or not. > Maybe the slaves must be the same version as the master. > > The main problem is that debian is too old, and we are afraid to use > PG_Upgrade because of too many version differences (11 < 17). > The fear is justified, since collation changes might corrupt text indices. > But maybe you have some good advice, how to do this with less complication. > Users can tolerate short downtimes, but not longer ones. > Logical replication, not physical replication. Works great if there's not many DDL changes. Another possibility is COPY TO / COPY FROM in chunks. This will work if most of the 1.2TB is inserted, and then never updated or deleted. That way, at cutover, you only need to COPY TO / COPY FROM and dump/restore (which is really a wrapper around COPY TO / COPY FROM) and "pg_restore --section=post-data". You (hopefully) know your system better than we do, so you'd have to decide which is better. -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Pgbackrest : Resumable backup of same type exists
Hi, Due to a backup failure in my Reposerver, the storage disk ran out of space, fixed later. I reschedule the cron job to take a full backup but it show for the last 3 hours 025-05-01 09:30:16.377 P00 WARN: resumable backup 20250425-115723F of same type exists -- invalid files will be removed then the backup will resume This message only I am seeing when looking at the backup log for my repo , is this normal ? How much time does it take to start actual backup again ? Or something wrong ? root@db1 ~]# cat /etc/pgbackrest/pgbackrest.conf [My_Repo] pg1-path=/var/lib/postgres/16/data pg1-port=5444 pg1-user=postgres pg-version-force=16 [global] repo1-host=10.x.x.x repo1-host-user=postgres archive-async=y spool-path=/var/spool/pgbackrest log-level-console=info log-level-file=debug #log-level-file=warn log-level-stderr=info delta=y [global:archive-get] process-max=2 [global:archive-push] process-max=4 Regards, Krishane
Re: Upgrading PG11 to PG17 without dump/restore
On 5/1/25 06:06, Durumdara wrote: Hello! There is a heavily used server, with older debian, and PG11. The data is more than 1,2 TB. The PG_Upgrade is not possible because of lesser space and too old debian. 1) Specify what the old and new versions of Debian are. 2) I don't understand what the following means: "The PG_Upgrade is not possible because of lesser space ..." . But this seems to be very hard, because we need to do this through an internet connection, and the data is too much. An Internet connection can be dial up speed or direct fiber speed, you will need to be more specific about the connection capabilities. Best regards dd -- Adrian Klaver adrian.kla...@aklaver.com
Upgrading PG11 to PG17 without dump/restore
Hello! There is a heavily used server, with older debian, and PG11. The data is more than 1,2 TB. The PG_Upgrade is not possible because of lesser space and too old debian. As we see now we have only one way to move this server. 1.) Installing a new server with actual debian. 2.) Installing the newest PG (17) on it. 3.) Stop work on one database. Dump it in the old, restore it in the new and start the work with that. So we can move them one by one. But this seems to be very hard, because we need to do this through an internet connection, and the data is too much. I have a question about it - is there a better way to do this? For example we make a new cluster element (a read only slave) with newest debian/PG, and use it to move the data in the background (replication). And then we rename it to master. But I don't know if it's possible or not. Maybe the slaves must be the same version as the master. The main problem is that debian is too old, and we are afraid to use PG_Upgrade because of too many version differences (11 < 17). But maybe you have some good advice, how to do this with less complication. Users can tolerate short downtimes, but not longer ones. Thank you! Best regards dd
Re: Pgbackrest : Resumable backup of same type exists
Hello, This warning message indicates that a *resumable backup*, labeled 20250425-115723F, already exists from a previous attempt. The backup system has detected it and will do the following: - *Remove any invalid or corrupted files* from that existing backup attempt. - *Resume* the backup from where it left off, rather than starting over. This is a *normal and expected message* if a previous backup was interrupted (due to system shutdown, network error, etc.). It helps avoid wasting time and resources by reusing valid parts of the incomplete backup. If you're concerned or just reviewing logs, you generally *don’t need to take action* unless: - The resume process fails. - The backup system repeatedly logs this message without completing. - You're unsure if the resumed backup will be reliable (e.g., after a crash). Would you like help verifying the integrity of the resumed backup once it finishes? Else, DELETE the failed backup and take/run fresh backup. rm -rf /var/lib/pgbackrest/backup/your_stanza_name/20250425-115723F Thanks & Regards, Abdul Sayeed On Thu, May 1, 2025 at 1:20 PM KK CHN wrote: > Hi, > Due to a backup failure in my Reposerver, the storage disk ran out of > space, fixed later. I reschedule the cron job to take a full backup but > it show for the last 3 hours > > 025-05-01 09:30:16.377 P00 WARN: resumable backup 20250425-115723F of > same type exists -- invalid files will be removed then the backup will > resume > > This message only I am seeing when looking at the backup log for my repo , > is this normal ? How much time does it take to start actual backup again ? > Or something wrong ? > > root@db1 ~]# cat /etc/pgbackrest/pgbackrest.conf > [My_Repo] > pg1-path=/var/lib/postgres/16/data > pg1-port=5444 > pg1-user=postgres > pg-version-force=16 > > [global] > repo1-host=10.x.x.x > repo1-host-user=postgres > > archive-async=y > spool-path=/var/spool/pgbackrest > > log-level-console=info > log-level-file=debug > #log-level-file=warn > log-level-stderr=info > delta=y > > [global:archive-get] > process-max=2 > > [global:archive-push] > process-max=4 > > Regards, > Krishane > > --
Re: Pgbackrest : Resumable backup of same type exists
On Thu, May 1, 2025 at 9:08 AM Abdul Sayeed wrote: > Hello, > > This warning message indicates that a *resumable backup*, > (plus lots more ChatGPT crap) Please do not use LLMs to answer questions here. If the original poster wanted that, they could have done it themselves. Further, you are polluting the pgsql-general archives with computer-generated answers which will in turn get slurped in again, leading to degradation. People posting here deserve real answers from real people. /rant Original poster, I wouldn't expect it to take three+ hours, but we don't know how large your backups are. Check /var/log/pgbackrest and see the latest log entry - it looks like you have "debug" level enabled, so it should be quite verbose. If the process is running but not writing anything to the logs, you might want to use strace against its PID to verify what it is doing (if anything). Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support
Is anyone up for hosting the online PG game "Schemaverse"?
Hi all, The PostgreSQL game "Schemaverse" was removed from the PostgreSQL website's links a few months ago because it no longer had hosting. Does anyone around have spare server/vm/something that could be used to host it (for free)? Details in the forwarded message below. :) Regards and best wishes, Justin Clift Original Message Subject: Schemaverse Date: 2025-04-21 13:01 From: Joshua McDougall To: jus...@postgresql.org Hi Justin, To answer your question, Schemaverse doesn’t take massive power to host. It needs a database and a small server for the front-end, which can (and has) run off a single virtual machine. I think the last one was 4 vCPUs and 16GB of memory but we’ve hosted off smaller too. The unique part of the setup tends to be its lacking security. All players are users on the database, so the pg_hba.conf needs to be flexible and the game needs enough permissions to create users. These two requirements have made most SaaS offerings non-compatible. Admittedly, the last time I investigated such offerings was years ago now, so it’s possible things have changed. If it’s possible that we can find schemaverse a new home, that would be wonderful. I still get the occasional inquiry asking about it, usually from students, and I’d love to have it back up for those looking to learn from it. Regardless though, thank you for reaching out! -Josh
Re: Upgrading PG11 to PG17 without dump/restore
On 2025-05-01 23:06, Durumdara wrote: The PG_Upgrade is not possible because of lesser space and too old debian. As a data point, pg_upgrade has an option to do an in-place upgrade which reuses the vast majority of the data files as they are on disk, rather than making a copy. It's useful for larger databases, and for situations where you don't have much space. BUT, you *do* have backups don't you? Because stuff *can* go wrong, even though it's not common. ;) Regards and best wishes, Justin Clift
Re: Pgbackrest : Resumable backup of same type exists
Hi Greg, My apologies but the above answer which i gave was not from chatgpt, I had face similar situation in one of our env. Deleting failed backup helped me. To be honest I did use chathpt to paraphrase my sentences before posting in general mail list. Thanks & Regards, Abdul Sayeed On Thu, 1 May, 2025, 11:13 pm Greg Sabino Mullane, wrote: > On Thu, May 1, 2025 at 9:08 AM Abdul Sayeed > wrote: > >> Hello, >> >> This warning message indicates that a *resumable backup*, >> > (plus lots more ChatGPT crap) > > Please do not use LLMs to answer questions here. If the original poster > wanted that, they could have done it themselves. Further, you are polluting > the pgsql-general archives with computer-generated answers which will in > turn get slurped in again, leading to degradation. People posting here > deserve real answers from real people. > > /rant > > Original poster, I wouldn't expect it to take three+ hours, but we don't > know how large your backups are. Check /var/log/pgbackrest and see the > latest log entry - it looks like you have "debug" level enabled, so it > should be quite verbose. If the process is running but not writing anything > to the logs, you might want to use strace against its PID to verify what it > is doing (if anything). > > Cheers, > Greg > > -- > Crunchy Data - https://www.crunchydata.com > Enterprise Postgres Software Products & Tech Support > >
Re: Pgbackrest : Resumable backup of same type exists
Thanks for the helpful hints. The backup finished successfully : 20250425-115723F timestamp start/stop: 2025-05-01 09:31:36+05:30 / 2025-05-01 14:13:08+ The log doesn't show anything other than (025-05-01 09:30:16.377 P00 WARN: resumable backup 20250425-115723F of same type exists -- invalid files will be removed then the backup will resume) is due to my Repo Server configuration waslog-level-file = warn in pgbackrest.conf, Its my mistake, I have enabled log-level-file=debug only in DB cluster initially. Now I hv changed in Repo_Server the log reports as expected. log-level-file=debug #log-level-file=warn Thank you, Krishane On Thu, May 1, 2025 at 11:13 PM Greg Sabino Mullane wrote: > On Thu, May 1, 2025 at 9:08 AM Abdul Sayeed > wrote: > >> Hello, >> >> This warning message indicates that a *resumable backup*, >> > (plus lots more ChatGPT crap) > > Please do not use LLMs to answer questions here. If the original poster > wanted that, they could have done it themselves. Further, you are polluting > the pgsql-general archives with computer-generated answers which will in > turn get slurped in again, leading to degradation. People posting here > deserve real answers from real people. > > /rant > > Original poster, I wouldn't expect it to take three+ hours, but we don't > know how large your backups are. Check /var/log/pgbackrest and see the > latest log entry - it looks like you have "debug" level enabled, so it > should be quite verbose. If the process is running but not writing anything > to the logs, you might want to use strace against its PID to verify what it > is doing (if anything). > > Cheers, > Greg > > -- > Crunchy Data - https://www.crunchydata.com > Enterprise Postgres Software Products & Tech Support > >
