pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMove database
6+ messages / 3 participants
[nested] [flat]
* Move database
@ 2020-11-10 13:48 Yambu <hyambu@gmail.com>
2020-11-10 14:11 ` Re: Move database Paul Förster <paul.foerster@gmail.com>
2020-11-10 14:12 ` Re: Move database Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Yambu @ 2020-11-10 13:48 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi
What is the best way to move a database from one VM to another without
losing records from updating applications?
regards
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move database
2020-11-10 13:48 Move database Yambu <hyambu@gmail.com>
@ 2020-11-10 14:11 ` Paul Förster <paul.foerster@gmail.com>
2020-11-10 14:16 ` Re: Move database Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Paul Förster @ 2020-11-10 14:11 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Yambu,
> On 10. Nov, 2020, at 14:48, Yambu <hyambu@gmail.com> wrote:
> What is the best way to move a database from one VM to another without losing records from updating applications?
I assume you want to minimize down time? If so, set up a streaming replication on the new VM and let PostgreSQL copy the whole database over by itself as a standby database. After copying is finished and both databases are completely in sync, then shut down the old (primary) database and promote the standby database. After that, you can delete the old database.
Assuming you can afford the down time, there is always the option of shutting the database down and scp or rsync it to the new VM and start it there.
If you run it on some external storage like mounted NFS filesystems, you can shut it down, umount the NFS volume(s) on the old VM, mount the NFS volume(s) on the new VM and start it there.
It really depends on your system (storage) architecture.
Cheers,
Paul
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move database
2020-11-10 13:48 Move database Yambu <hyambu@gmail.com>
2020-11-10 14:11 ` Re: Move database Paul Förster <paul.foerster@gmail.com>
@ 2020-11-10 14:16 ` Ron <ronljohnsonjr@gmail.com>
2020-11-10 14:22 ` Re: Move database Yambu <hyambu@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ron @ 2020-11-10 14:16 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/10/20 8:11 AM, Paul Förster wrote:
> Hi Yambu,
>
>> On 10. Nov, 2020, at 14:48, Yambu <hyambu@gmail.com> wrote:
>> What is the best way to move a database from one VM to another without losing records from updating applications?
> I assume you want to minimize down time? If so, set up a streaming replication on the new VM and let PostgreSQL copy the whole database over by itself as a standby database. After copying is finished and both databases are completely in sync, then shut down the old (primary) database and promote the standby database. After that, you can delete the old database.
>
> Assuming you can afford the down time, there is always the option of shutting the database down and scp or rsync it to the new VM and start it there.
>
> If you run it on some external storage like mounted NFS filesystems, you can shut it down, umount the NFS volume(s) on the old VM, mount the NFS volume(s) on the new VM and start it there.
>
> It really depends on your system (storage) architecture.
Given this set of circumstances, they could dismount the LUNs from VM-old,
and mount them on VM-new:
* it's on a SAN,
* they're keeping the same version number,
* the database files are on dedicated mount points.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move database
2020-11-10 13:48 Move database Yambu <hyambu@gmail.com>
2020-11-10 14:11 ` Re: Move database Paul Förster <paul.foerster@gmail.com>
2020-11-10 14:16 ` Re: Move database Ron <ronljohnsonjr@gmail.com>
@ 2020-11-10 14:22 ` Yambu <hyambu@gmail.com>
2020-11-10 14:24 ` Re: Move database Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Yambu @ 2020-11-10 14:22 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi
Same version of database, the old server will remain running with other
databases, we just want to move one database.
regards
On Tue, Nov 10, 2020 at 4:16 PM Ron <ronljohnsonjr@gmail.com> wrote:
> On 11/10/20 8:11 AM, Paul Förster wrote:
> > Hi Yambu,
> >
> >> On 10. Nov, 2020, at 14:48, Yambu <hyambu@gmail.com> wrote:
> >> What is the best way to move a database from one VM to another without
> losing records from updating applications?
> > I assume you want to minimize down time? If so, set up a streaming
> replication on the new VM and let PostgreSQL copy the whole database over
> by itself as a standby database. After copying is finished and both
> databases are completely in sync, then shut down the old (primary) database
> and promote the standby database. After that, you can delete the old
> database.
> >
> > Assuming you can afford the down time, there is always the option of
> shutting the database down and scp or rsync it to the new VM and start it
> there.
> >
> > If you run it on some external storage like mounted NFS filesystems, you
> can shut it down, umount the NFS volume(s) on the old VM, mount the NFS
> volume(s) on the new VM and start it there.
> >
> > It really depends on your system (storage) architecture.
>
> Given this set of circumstances, they could dismount the LUNs from VM-old,
> and mount them on VM-new:
> * it's on a SAN,
> * they're keeping the same version number,
> * the database files are on dedicated mount points.
>
> --
> Angular momentum makes the world go 'round.
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move database
2020-11-10 13:48 Move database Yambu <hyambu@gmail.com>
2020-11-10 14:11 ` Re: Move database Paul Förster <paul.foerster@gmail.com>
2020-11-10 14:16 ` Re: Move database Ron <ronljohnsonjr@gmail.com>
2020-11-10 14:22 ` Re: Move database Yambu <hyambu@gmail.com>
@ 2020-11-10 14:24 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Ron @ 2020-11-10 14:24 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/10/20 8:22 AM, Yambu wrote:
> Hi
>
> Same version of database, the old server will remain running with other
> databases, we just want to move one database.
If that's the case, then you either must:
* do a pg_dump/pg_restore, or
* depending on the version, logically replicate that one database.
>
> regards
>
> On Tue, Nov 10, 2020 at 4:16 PM Ron <ronljohnsonjr@gmail.com
> <mailto:ronljohnsonjr@gmail.com>> wrote:
>
> On 11/10/20 8:11 AM, Paul Förster wrote:
> > Hi Yambu,
> >
> >> On 10. Nov, 2020, at 14:48, Yambu <hyambu@gmail.com
> <mailto:hyambu@gmail.com>> wrote:
> >> What is the best way to move a database from one VM to another
> without losing records from updating applications?
> > I assume you want to minimize down time? If so, set up a streaming
> replication on the new VM and let PostgreSQL copy the whole database
> over by itself as a standby database. After copying is finished and
> both databases are completely in sync, then shut down the old
> (primary) database and promote the standby database. After that, you
> can delete the old database.
> >
> > Assuming you can afford the down time, there is always the option of
> shutting the database down and scp or rsync it to the new VM and start
> it there.
> >
> > If you run it on some external storage like mounted NFS filesystems,
> you can shut it down, umount the NFS volume(s) on the old VM, mount
> the NFS volume(s) on the new VM and start it there.
> >
> > It really depends on your system (storage) architecture.
>
> Given this set of circumstances, they could dismount the LUNs from
> VM-old,
> and mount them on VM-new:
> * it's on a SAN,
> * they're keeping the same version number,
> * the database files are on dedicated mount points.
>
> --
> Angular momentum makes the world go 'round.
>
>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move database
2020-11-10 13:48 Move database Yambu <hyambu@gmail.com>
@ 2020-11-10 14:12 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Ron @ 2020-11-10 14:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/10/20 7:48 AM, Yambu wrote:
> Hi
>
> What is the best way to move a database from one VM to another without
> losing records from updating applications?
>
> regards
How much downtime can you suffer?
Are you staying at the same version, or upgrading, at the same?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-11-10 14:24 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-10 13:48 Move database Yambu <hyambu@gmail.com>
2020-11-10 14:11 ` Paul Förster <paul.foerster@gmail.com>
2020-11-10 14:16 ` Ron <ronljohnsonjr@gmail.com>
2020-11-10 14:22 ` Yambu <hyambu@gmail.com>
2020-11-10 14:24 ` Ron <ronljohnsonjr@gmail.com>
2020-11-10 14:12 ` Ron <ronljohnsonjr@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