Subject: Re: Move database
To: pgsql-admin@lists.postgresql.org
References: 
 <CALhHtcAyu3Q-YnFjgGxkZc9dBkCFLvi8-E1AJ44ayA=SSuZTJQ@mail.gmail.com>
 <A631D97B-6644-4935-849A-F4E8E57C6F31@gmail.com>
 <100b9c34-6b52-c8a6-91de-3cbff6de87c6@gmail.com>
 <CALhHtcCbOm7J9FCU9LVGbu_F36WT7RecsB9sFC0S4O9EgHH=Kw@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <07a5cf21-76f6-b47f-bd7c-22e1241e9f3a@gmail.com>
Date: Tue, 10 Nov 2020 08:24:53 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: 
 <CALhHtcCbOm7J9FCU9LVGbu_F36WT7RecsB9sFC0S4O9EgHH=Kw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------40D362D2EBD9BF8E303ADEFF"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------40D362D2EBD9BF8E303ADEFF
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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.

--------------40D362D2EBD9BF8E303ADEFF
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 11/10/20 8:22 AM, Yambu wrote:<br>
    <blockquote type="cite"
cite="mid:CALhHtcCbOm7J9FCU9LVGbu_F36WT7RecsB9sFC0S4O9EgHH=Kw@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div>Hi</div>
        <div><br>
        </div>
        <div>Same version of database, the old server will remain
          running with other databases, we just want to move one
          database.</div>
      </div>
    </blockquote>
    <br>
    If that's the case, then you either must:<br>
    * do a pg_dump/pg_restore, or<br>
    * depending on the version, logically replicate that one database.<br>
    <br>
    <blockquote type="cite"
cite="mid:CALhHtcCbOm7J9FCU9LVGbu_F36WT7RecsB9sFC0S4O9EgHH=Kw@mail.gmail.com">
      <div dir="ltr">
        <div><br>
        </div>
        <div>regards</div>
      </div>
      <br>
      <div class="gmail_quote">
        <div class="gmail_attr" dir="ltr">On Tue, Nov 10, 2020 at 4:16
          PM Ron &lt;<a href="mailto:ronljohnsonjr@gmail.com"
            moz-do-not-send="true">ronljohnsonjr@gmail.com</a>&gt;
          wrote:<br>
        </div>
        <blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;padding-left:1ex;border-left-color:rgb(204,204,204);border-left-width:1px;border-left-style:solid">On
          11/10/20 8:11 AM, Paul Förster wrote:<br>
          &gt; Hi Yambu,<br>
          &gt;<br>
          &gt;&gt; On 10. Nov, 2020, at 14:48, Yambu &lt;<a
            href="mailto:hyambu@gmail.com" target="_blank"
            moz-do-not-send="true">hyambu@gmail.com</a>&gt; wrote:<br>
          &gt;&gt; What is the best way to move a database from one VM
          to another without losing records from updating applications?<br>
          &gt; 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.<br>
          &gt;<br>
          &gt; 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.<br>
          &gt;<br>
          &gt; 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.<br>
          &gt;<br>
          &gt; It really depends on your system (storage) architecture.<br>
          <br>
          Given this set of circumstances, they could dismount the LUNs
          from VM-old, <br>
          and mount them on VM-new:<br>
          * it's on a SAN,<br>
          * they're keeping the same version number,<br>
          * the database files are on dedicated mount points.<br>
          <br>
          -- <br>
          Angular momentum makes the world go 'round.<br>
          <br>
          <br>
        </blockquote>
      </div>
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------40D362D2EBD9BF8E303ADEFF--