pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres storage migration
13+ messages / 5 participants
[nested] [flat]
* Postgres storage migration
@ 2023-12-08 09:43 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 3 replies; 13+ messages in thread
From: Rajesh Kumar @ 2023-12-08 09:43 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-08 10:09 Paul Smith* <paul@pscs.co.uk>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 1 reply; 13+ messages in thread
From: Paul Smith* @ 2023-12-08 10:09 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-08 11:33 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 2 replies; 13+ messages in thread
From: Ron Johnson @ 2023-12-08 11:33 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, Dec 8, 2023 at 4:44 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
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.
>
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.)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-08 12:37 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Paul Smith* <paul@pscs.co.uk>
0 siblings, 1 reply; 13+ messages in thread
From: Rajesh Kumar @ 2023-12-08 12:37 UTC (permalink / raw)
To: Paul Smith* <paul@pscs.co.uk>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-08 13:03 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Ron Johnson @ 2023-12-08 13:03 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Paul Smith* <paul@pscs.co.uk>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-08 14:57 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Scott Ribe @ 2023-12-08 14:57 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-08 15:26 ẞ. Shafi <shafi1212212@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 0 replies; 13+ messages in thread
From: ẞ. Shafi @ 2023-12-08 15:26 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Please stop sending these emails to me
On Fri, Dec 8, 2023, 3:14 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
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.
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-08 19:05 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Rajesh Kumar @ 2023-12-08 19:05 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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:
>
>> 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.
>>
>
> 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.)
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-08 20:00 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Ron Johnson @ 2023-12-08 20:00 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 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:
>>
>>> 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.
>>>
>>
>> 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.)
>>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-09 02:56 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Rajesh Kumar @ 2023-12-09 02:56 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 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:
>>>
>>>> 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.
>>>>
>>>
>>> 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.)
>>>
>>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-09 05:08 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Ron Johnson @ 2023-12-09 05:08 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 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:
>>>>
>>>>> 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.
>>>>>
>>>>
>>>> 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.)
>>>>
>>>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-09 16:12 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Scott Ribe @ 2023-12-09 16:12 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Postgres storage migration
@ 2023-12-10 20:27 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 13+ messages in thread
From: Rajesh Kumar @ 2023-12-10 20:27 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2023-12-10 20:27 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-08 09:43 Postgres storage migration Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-08 10:09 ` Paul Smith* <paul@pscs.co.uk>
2023-12-08 12:37 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-08 13:03 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-08 11:33 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-08 14:57 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-12-08 19:05 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-08 20:00 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-09 02:56 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-09 05:08 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-09 16:12 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-12-10 20:27 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-08 15:26 ` ẞ. Shafi <shafi1212212@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox