Message-ID: <20fd6e7a-73ff-4d15-a2e1-a44d5a4aa299@pscs.co.uk>
Date: Fri, 8 Dec 2023 10:09:52 +0000
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Postgres storage migration
Content-Language: en-GB
To: pgsql-admin@lists.postgresql.org
References: 
 <CAJk5Ata3U1_BaLHiFoa31OkJ2RkmEuHxHWQi1gUNAqR_j-AS2Q@mail.gmail.com>
From: Paul Smith* <paul@pscs.co.uk>
In-Reply-To: 
 <CAJk5Ata3U1_BaLHiFoa31OkJ2RkmEuHxHWQi1gUNAqR_j-AS2Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20fd6e7a-73ff-4d15-a2e1-a44d5a4aa299%40pscs.co.uk>
Precedence: bulk

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