Обсуждение: Postgres storage migration
Hi
We are using openshift environment and postgres version 15.2. We want to change storage from ceph to local storage. So, Storage team made default storage as local. Now, I created a new cluster with same postgres version and I am planning to take backup from old cluster to new cluster. Size is 100gb. Ram 24gb, cpu 2.
My question is, is there a combination of pg_dump and pg_restore that takes less time to complete the task?
Last time it took more than 8hours. We were taking schema only dump using dumpall . Then data only backup using pg_dump in directory format.
On 08/12/2023 09:43, Rajesh Kumar wrote: > Hi > > We are using openshift environment and postgres version 15.2. We want > to change storage from ceph to local storage. So, Storage team made > default storage as local. Now, I created a new cluster with same > postgres version and I am planning to take backup from old cluster to > new cluster. Size is 100gb. Ram 24gb, cpu 2. > > My question is, is there a combination of pg_dump and pg_restore that > takes less time to complete the task? > > Last time it took more than 8hours. We were taking schema only dump > using dumpall . Then data only backup using pg_dump in directory format. If you are using the same (major) version of PostgreSQL, you are moving the whole cluster, and can stop the services on both ends first, then you can: - stop the services (both old and new) - empty the data directory in the new location - copy the whole data directory from the old location to the new location (using copy, rsync, scp, whatever) - start the service on the new location Obviously backup everything before doing anything destructive. If you want to do it while the database is still running, then use replication and fail-over instead. Paul
On Fri, Dec 8, 2023 at 4:44 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
HiWe are using openshift environment and postgres version 15.2. We want to change storage from ceph to local storage. So, Storage team made default storage as local. Now, I created a new cluster with same postgres version and I am planning to take backup from old cluster to new cluster. Size is 100gb. Ram 24gb, cpu 2.My question is, is there a combination of pg_dump and pg_restore that takes less time to complete the task?Last time it took more than 8hours. We were taking schema only dump using dumpall . Then data only backup using pg_dump in directory format.
8 hours is really slow for just 100GB of database. What exact command are you using?
Paul Smith is right, though: Just shut down the instance, copy the files, and start up the instance with new "-D" location.
(Will need to edit postgresql.conf if it defines file locations.)
In this case , basebackup is not required? Just rsync the directory /pgdata/pg15 ? I have one master and one replica.
On Fri, 8 Dec 2023, 15:45 Paul Smith*, <paul@pscs.co.uk> wrote:
On 08/12/2023 09:43, Rajesh Kumar wrote:
> Hi
>
> We are using openshift environment and postgres version 15.2. We want
> to change storage from ceph to local storage. So, Storage team made
> default storage as local. Now, I created a new cluster with same
> postgres version and I am planning to take backup from old cluster to
> new cluster. Size is 100gb. Ram 24gb, cpu 2.
>
> My question is, is there a combination of pg_dump and pg_restore that
> takes less time to complete the task?
>
> Last time it took more than 8hours. We were taking schema only dump
> using dumpall . Then data only backup using pg_dump in directory format.
If you are using the same (major) version of PostgreSQL, you are moving
the whole cluster, and can stop the services on both ends first, then
you can:
- stop the services (both old and new)
- empty the data directory in the new location
- copy the whole data directory from the old location to the new
location (using copy, rsync, scp, whatever)
- start the service on the new location
Obviously backup everything before doing anything destructive.
If you want to do it while the database is still running, then use
replication and fail-over instead.
Paul
Postgresql "data files" do not store their directory locations. Move $PGDATA anywhere you want as long as the instance is stopped.
On Fri, Dec 8, 2023 at 7:52 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
In this case , basebackup is not required? Just rsync the directory /pgdata/pg15 ? I have one master and one replica.On Fri, 8 Dec 2023, 15:45 Paul Smith*, <paul@pscs.co.uk> wrote:On 08/12/2023 09:43, Rajesh Kumar wrote:
> Hi
>
> We are using openshift environment and postgres version 15.2. We want
> to change storage from ceph to local storage. So, Storage team made
> default storage as local. Now, I created a new cluster with same
> postgres version and I am planning to take backup from old cluster to
> new cluster. Size is 100gb. Ram 24gb, cpu 2.
>
> My question is, is there a combination of pg_dump and pg_restore that
> takes less time to complete the task?
>
> Last time it took more than 8hours. We were taking schema only dump
> using dumpall . Then data only backup using pg_dump in directory format.
If you are using the same (major) version of PostgreSQL, you are moving
the whole cluster, and can stop the services on both ends first, then
you can:
- stop the services (both old and new)
- empty the data directory in the new location
- copy the whole data directory from the old location to the new
location (using copy, rsync, scp, whatever)
- start the service on the new location
Obviously backup everything before doing anything destructive.
If you want to do it while the database is still running, then use
replication and fail-over instead.
Paul
> On Dec 8, 2023, at 4:33 AM, Ron Johnson <ronljohnsonjr@gmail.com> wrote: > > 8 hours is really slow for just 100GB of database. Indeed, but if they have an inadequate Ceph config, it might take that long no matter how they move the data.
Please stop sending these emails to me
On Fri, Dec 8, 2023, 3:14 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
HiWe are using openshift environment and postgres version 15.2. We want to change storage from ceph to local storage. So, Storage team made default storage as local. Now, I created a new cluster with same postgres version and I am planning to take backup from old cluster to new cluster. Size is 100gb. Ram 24gb, cpu 2.My question is, is there a combination of pg_dump and pg_restore that takes less time to complete the task?Last time it took more than 8hours. We were taking schema only dump using dumpall . Then data only backup using pg_dump in directory format.
Pg_dump -a -v -j2 -Z0 -Fd -d dbname -f dumpfilename
On Fri, 8 Dec, 2023, 5:04 PM Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
On Fri, Dec 8, 2023 at 4:44 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:HiWe are using openshift environment and postgres version 15.2. We want to change storage from ceph to local storage. So, Storage team made default storage as local. Now, I created a new cluster with same postgres version and I am planning to take backup from old cluster to new cluster. Size is 100gb. Ram 24gb, cpu 2.My question is, is there a combination of pg_dump and pg_restore that takes less time to complete the task?Last time it took more than 8hours. We were taking schema only dump using dumpall . Then data only backup using pg_dump in directory format.8 hours is really slow for just 100GB of database. What exact command are you using?Paul Smith is right, though: Just shut down the instance, copy the files, and start up the instance with new "-D" location.(Will need to edit postgresql.conf if it defines file locations.)
In --format=d mode,--file= is the directory where the data files are stored, not regular file.
This works for me:
pg_dump -j${Threads} -Z${ZLvl} -v -C -Fd --file=${BackupDir}/$DB $DB 2> ${DB}_pgdump.log
(Also, why are you just dumping the data, if you need to migrate the whole database?
On Fri, Dec 8, 2023 at 2:05 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Pg_dump -a -v -j2 -Z0 -Fd -d dbname -f dumpfilenameOn Fri, 8 Dec, 2023, 5:04 PM Ron Johnson, <ronljohnsonjr@gmail.com> wrote:On Fri, Dec 8, 2023 at 4:44 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:HiWe are using openshift environment and postgres version 15.2. We want to change storage from ceph to local storage. So, Storage team made default storage as local. Now, I created a new cluster with same postgres version and I am planning to take backup from old cluster to new cluster. Size is 100gb. Ram 24gb, cpu 2.My question is, is there a combination of pg_dump and pg_restore that takes less time to complete the task?Last time it took more than 8hours. We were taking schema only dump using dumpall . Then data only backup using pg_dump in directory format.8 hours is really slow for just 100GB of database. What exact command are you using?Paul Smith is right, though: Just shut down the instance, copy the files, and start up the instance with new "-D" location.(Will need to edit postgresql.conf if it defines file locations.)
We are facing error while restoring..So, I used dumpall to dump only schema and restored schema first . Then dumping data.
On Sat, 9 Dec 2023, 01:30 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
In --format=d mode,--file= is the directory where the data files are stored, not regular file.This works for me:pg_dump -j${Threads} -Z${ZLvl} -v -C -Fd --file=${BackupDir}/$DB $DB 2> ${DB}_pgdump.log(Also, why are you just dumping the data, if you need to migrate the whole database?On Fri, Dec 8, 2023 at 2:05 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:Pg_dump -a -v -j2 -Z0 -Fd -d dbname -f dumpfilenameOn Fri, 8 Dec, 2023, 5:04 PM Ron Johnson, <ronljohnsonjr@gmail.com> wrote:On Fri, Dec 8, 2023 at 4:44 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:HiWe are using openshift environment and postgres version 15.2. We want to change storage from ceph to local storage. So, Storage team made default storage as local. Now, I created a new cluster with same postgres version and I am planning to take backup from old cluster to new cluster. Size is 100gb. Ram 24gb, cpu 2.My question is, is there a combination of pg_dump and pg_restore that takes less time to complete the task?Last time it took more than 8hours. We were taking schema only dump using dumpall . Then data only backup using pg_dump in directory format.8 hours is really slow for just 100GB of database. What exact command are you using?Paul Smith is right, though: Just shut down the instance, copy the files, and start up the instance with new "-D" location.(Will need to edit postgresql.conf if it defines file locations.)
You're not being very helpful to the people who are trying to help you.
This works for me every time:
Source:
pg_dumpall -gvf ${BackupDir}/globals.sql 2> /dev/null
pg_dump -j ${Threads} -Z${ZLvl} -v -C -Fd --file=${SrcBackupDir}/$DB $DB 2> ${DB}_pgdump.log
Destination:
(Edit globals.sql and apply only the required statements to the target instance.)
pg_restore -v --jobs=$Threads --clean --create -Fd --dbname=postgres ${TargetBackupDir}/$DB
On Fri, Dec 8, 2023 at 11:31 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
We are facing error while restoring..So, I used dumpall to dump only schema and restored schema first . Then dumping data.On Sat, 9 Dec 2023, 01:30 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:In --format=d mode,--file= is the directory where the data files are stored, not regular file.This works for me:pg_dump -j${Threads} -Z${ZLvl} -v -C -Fd --file=${BackupDir}/$DB $DB 2> ${DB}_pgdump.log(Also, why are you just dumping the data, if you need to migrate the whole database?On Fri, Dec 8, 2023 at 2:05 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:Pg_dump -a -v -j2 -Z0 -Fd -d dbname -f dumpfilenameOn Fri, 8 Dec, 2023, 5:04 PM Ron Johnson, <ronljohnsonjr@gmail.com> wrote:On Fri, Dec 8, 2023 at 4:44 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:HiWe are using openshift environment and postgres version 15.2. We want to change storage from ceph to local storage. So, Storage team made default storage as local. Now, I created a new cluster with same postgres version and I am planning to take backup from old cluster to new cluster. Size is 100gb. Ram 24gb, cpu 2.My question is, is there a combination of pg_dump and pg_restore that takes less time to complete the task?Last time it took more than 8hours. We were taking schema only dump using dumpall . Then data only backup using pg_dump in directory format.8 hours is really slow for just 100GB of database. What exact command are you using?Paul Smith is right, though: Just shut down the instance, copy the files, and start up the instance with new "-D" location.(Will need to edit postgresql.conf if it defines file locations.)
> On Dec 8, 2023, at 7:56 PM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote: > > We are facing error while restoring What error? It would really help if you'd give us the full information, instead of having us drag it out of you one tidbit at a time.
Error with pg_restore is secondary. Let's forget that for now.
I am using Openshift platform, ceph storage. We are changing the storage to local. So, we created a cluster in new Local storage. All I want to know is how to migrate data from one cluster to another cluster in less downtime. 100gb is taking more than 8hrs for backup and restore.
1. Pg_dump and pg_restore good? If so directory format or which format is good?
2. Or pgbasebackup is possible in a customised Openshift env?
On Sat, 9 Dec, 2023, 9:42 PM Scott Ribe, <scott_ribe@elevated-dev.com> wrote:
> On Dec 8, 2023, at 7:56 PM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> We are facing error while restoring
What error?
It would really help if you'd give us the full information, instead of having us drag it out of you one tidbit at a time.