Thread: How to move a 11.4 cluster to another Linux host, but empty?
Hello,
I've a 11.4 cluster on a Linux host with 3 production database, all
below /data/postgresql11.4/ (i.e. the cluster itself is
/data/postgresql11.4/data) fully configured an running fine. I want to
move it "cold" (i.e. when it is shutdown) by tar to another host, BUT
without the database (due to PII and GDPR relevant data). Between the
source and target host there is no network.
Of course I could, after moving all the PostgreSQL software (all is below a top
level directory /usr/local/sisis-pap/pgsql, compiled by me) and setup the
cluster from scratch on the other host, but I wanted to have it all in one shoot by tar.
Is there a way to exclude the databases from the move by tar, or by some
sophisticated pg_* command?
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Hi Matthias, > On 01. May, 2020, at 10:10, Matthias Apitz <guru@unixarea.de> wrote: > > > Hello, > > I've a 11.4 cluster on a Linux host with 3 production database, all > below /data/postgresql11.4/ (i.e. the cluster itself is > /data/postgresql11.4/data) fully configured an running fine. I want to > move it "cold" (i.e. when it is shutdown) by tar to another host, BUT > without the database (due to PII and GDPR relevant data). Between the > source and target host there is no network. > > Of course I could, after moving all the PostgreSQL software (all is below a top > level directory /usr/local/sisis-pap/pgsql, compiled by me) and setup the > cluster from scratch on the other host, but I wanted to have it all in one shoot by tar. > > Is there a way to exclude the databases from the move by tar, or by some > sophisticated pg_* command? > > Thanks > > matthias you may have a look at the tar man page. tar in Linux has the --exclude=PATTERN option. You may specify the PGDATA directorythere. Alternatively you may specify each directory inside the software directory without explicitly specifying subdirectories toinclude: tar cvf postgresql11.4.tar /data/postgresql11.4/bin /data/postgresql11.4/share ... Also, why do you have the PGDATA directory inside the software home directory? That makes no sense and only leads to problemswhen updating or, in your case, moving. We use: /data/postgres/xx.x <= with xx.x as PostgreSQL version /data/<some-prefix>/<cluster_name> <= as PGDATA This way, we never have any trouble doing something with the software or database cluster independently from each other. Hope, this helps. Cheers, Paul
Hi Paul,
El día viernes, mayo 01, 2020 a las 10:23:37a. m. +0200, Paul Förster escribió:
>
> you may have a look at the tar man page. tar in Linux has the --exclude=PATTERN option.
I do know the man page(s) of tar ant the --exclude=PATTERN.
> You may specify the PGDATA directory there.
This is, what I wanted to know: that the cluster can be moved without
the PGDATA dir and still works fine.
> Also, why do you have the PGDATA directory inside the software home directory? That makes no sense and only leads to
problemswhen updating or, in your case, moving.
>
I don't. The software is below
/usr/local/sisis-pap/pgsql
and the cluster is below
/data/postgres11.4/data.
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Hi Matthias, > On 01. May, 2020, at 10:31, Matthias Apitz <guru@unixarea.de> wrote: > > I don't. The software is below > > /usr/local/sisis-pap/pgsql > > and the cluster is below > > /data/postgres11.4/data. in this case, all you'd have to do is: tar cvf software.tar /usr/local/sisis-pap/pgsql to only copy the software. You don't even need to stop the database cluster for this because nothing is ever changed insidethe software tree by a running database cluster. Changes are only done in PGDATA. Cheers, Paul
El día viernes, mayo 01, 2020 a las 10:31:48a. m. +0200, Matthias Apitz escribió:
> Hi Paul,
>
> El día viernes, mayo 01, 2020 a las 10:23:37a. m. +0200, Paul Förster escribió:
>
> >
> > you may have a look at the tar man page. tar in Linux has the --exclude=PATTERN option.
>
> I do know the man page(s) of tar ant the --exclude=PATTERN.
>
> > You may specify the PGDATA directory there.
>
> This is, what I wanted to know: that the cluster can be moved without
> the PGDATA dir and still works fine.
>
This will not work! The cluster was created with:
$ /usr/local/sisis-pap/pgsql/bin/pg_ctl -D /data/prosgresql11/data initdb
and consequently, PGDATA is /data/prosgresql11/data. This directory contains a
lot of subirs and config files:
srap32dxr1:/home/sisis/guru # du -s /data/postgresql11/data/* | sort -n
4 /data/postgresql11/data/PG_VERSION
4 /data/postgresql11/data/pg_commit_ts
4 /data/postgresql11/data/pg_dynshmem
4 /data/postgresql11/data/pg_ident.conf
4 /data/postgresql11/data/pg_replslot
4 /data/postgresql11/data/pg_serial
4 /data/postgresql11/data/pg_snapshots
4 /data/postgresql11/data/pg_stat
4 /data/postgresql11/data/pg_tblspc
4 /data/postgresql11/data/pg_twophase
4 /data/postgresql11/data/postgresql.auto.conf
4 /data/postgresql11/data/postmaster.opts
4 /data/postgresql11/data/postmaster.pid
8 /data/postgresql11/data/pg_hba.conf
12 /data/postgresql11/data/pg_notify
16 /data/postgresql11/data/pg_logical
24 /data/postgresql11/data/postgresql.conf
28 /data/postgresql11/data/pg_multixact
196 /data/postgresql11/data/pg_subtrans
732 /data/postgresql11/data/pg_stat_tmp
940 /data/postgresql11/data/global
1328 /data/postgresql11/data/pg_xact
76076 /data/postgresql11/data/serverlog.20200414
81988 /data/postgresql11/data/pg_wal
90268 /data/postgresql11/data/serverlog
16672568 /data/postgresql11/data/base
The databases (or it least their content) are below /data/postgresql11/data/base
(because of the size of ~81 GByte).
So the question remains: What I have to move by tar to get the cluster
setup on the new host, but without the databases in it?
If this is not possible, I will only copy over the software from /usr/local/sisis-pap/pgsql/
and create the cluster from scratch.
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Hi Matthias, > On 02. May, 2020, at 12:23, Matthias Apitz <guru@unixarea.de> wrote: > $ /usr/local/sisis-pap/pgsql/bin/pg_ctl -D /data/prosgresql11/data initdb I don't get it. Is the setup now as follows /data/postgresql11 <= software with bin, lib64, share, etc... /data/postgresql11/data <= PGDATA or /usr/local/sisis-pap/pgsql <= software with bin, lib64, share, etc... /data/postgresql11/data <= PGDATA ? In the first case, your tar command would be: tar cvf /tmp/postgresql11.tar --exclude=/data/postgresql11/data /data/postgresql11 Note that the --exclude option must appear BEFORE the directory tree to archive in the command line! => man tar In the second case, your tar command would be: tar cvf /tmp/postgresql11.tar /usr/local/sisis-pap/pgsql to package the software only without the database. Cheers, Paul
El día sábado, mayo 02, 2020 a las 03:23:52p. m. +0200, Paul Förster escribió:
> /usr/local/sisis-pap/pgsql <= software with bin, lib64, share, etc...
> /data/postgresql11/data <= PGDATA
Exactly, his is the setup above.
I will move /usr/local/sisis-pap/pgsql by tar; and I want to move some parts(!)
of the cluster /data/postgresql11/data (configuration etc.), but not the databases
included in /data/postgresql11/data, like the cluster(!) in PGDATA was before having
created any database(!) in it.
As I said from the beginning: moving the cluster but without the
databases in it.
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Hi Matthias, > On 02. May, 2020, at 16:30, Matthias Apitz <guru@unixarea.de> wrote: > > El día sábado, mayo 02, 2020 a las 03:23:52p. m. +0200, Paul Förster escribió: > >> /usr/local/sisis-pap/pgsql <= software with bin, lib64, share, etc... >> /data/postgresql11/data <= PGDATA > > Exactly, his is the setup above. > > I will move /usr/local/sisis-pap/pgsql by tar; and I want to move some parts(!) > of the cluster /data/postgresql11/data (configuration etc.), but not the databases > included in /data/postgresql11/data, like the cluster(!) in PGDATA was before having > created any database(!) in it. > > As I said from the beginning: moving the cluster but without the > databases in it. you don't want to move the database cluster in this case! Add the database cluster's config files postgresql.conf, pg_hba.conf, pg_ident.conf or whatever to the tar file. Then, onthe new host, do an initdb and after that, copy the old config files into the new PGDATA. Then start the new (empty andfree of databases) database cluster. So: tar cvf /tmp/postgresql11.tar /usr/local/sisis-pap/pgsql /data/postgresql11/*.conf Cheers, Paul
On 5/2/20 7:30 AM, Matthias Apitz wrote: > El día sábado, mayo 02, 2020 a las 03:23:52p. m. +0200, Paul Förster escribió: > >> /usr/local/sisis-pap/pgsql <= software with bin, lib64, share, etc... >> /data/postgresql11/data <= PGDATA > > Exactly, his is the setup above. > > I will move /usr/local/sisis-pap/pgsql by tar; and I want to move some parts(!) > of the cluster /data/postgresql11/data (configuration etc.), but not the databases > included in /data/postgresql11/data, like the cluster(!) in PGDATA was before having > created any database(!) in it. That won't work for the following reasons: 1) A lot of the files/dirs included in /data/postgresql11/data are specific to the files in /data/postgresql11/data/base as they where created over time. They will fail when you try to merge them with new data. More importantly see 2) below. 2) To create a new base/ you will need to initdb data/ and that is not possible as initdb will not work on a populated data/. > > As I said from the beginning: moving the cluster but without the > databases in it. > > Thanks > > matthias > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian, > On 02. May, 2020, at 16:59, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > 2) To create a new base/ you will need to initdb data/ and that is not possible as initdb will not work on a populateddata/. you do the initdb and then copy the *.conf files from the tar over the newly created ones. After that, launch the cluster. Cheers, Paul
On 5/2/20 8:02 AM, Paul Förster wrote: > Hi Adrian, > >> On 02. May, 2020, at 16:59, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> 2) To create a new base/ you will need to initdb data/ and that is not possible as initdb will not work on a populateddata/. > > you do the initdb and then copy the *.conf files from the tar over the newly created ones. After that, launch the cluster. Yeah I know, I do that on a fairly regular basis. The intent was to let the OP know that ripping base/ out of cluster and transplanting a new one in is not possible. > > Cheers, > Paul > -- Adrian Klaver adrian.klaver@aklaver.com
El día sábado, mayo 02, 2020 a las 08:23:52a. m. -0700, Adrian Klaver escribió:
> On 5/2/20 8:02 AM, Paul Förster wrote:
> > Hi Adrian,
> >
> >> On 02. May, 2020, at 16:59, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> >> 2) To create a new base/ you will need to initdb data/ and that is not possible as initdb will not work on a
populateddata/.
> >
> > you do the initdb and then copy the *.conf files from the tar over the newly created ones. After that, launch the
cluster.
>
> Yeah I know, I do that on a fairly regular basis. The intent was to let
> the OP know that ripping base/ out of cluster and transplanting a new
> one in is not possible.
OK, lesson learned: Next time I will do a tar backup of PGDATA after
the configuration of the cluster and before creating any database in it.
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Hi Matthias, > On 02. May, 2020, at 20:58, Matthias Apitz <guru@unixarea.de> wrote: > OK, lesson learned: Next time I will do a tar backup of PGDATA after > the configuration of the cluster and before creating any database in it. that tar would still contain too much. :-) You really only need the config files. All else is created by initdb. Cheers, Paul
On 5/2/20 11:58 AM, Matthias Apitz wrote: > El día sábado, mayo 02, 2020 a las 08:23:52a. m. -0700, Adrian Klaver escribió: > >> On 5/2/20 8:02 AM, Paul Förster wrote: >>> Hi Adrian, >>> >>>> On 02. May, 2020, at 16:59, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >>>> 2) To create a new base/ you will need to initdb data/ and that is not possible as initdb will not work on a populateddata/. >>> >>> you do the initdb and then copy the *.conf files from the tar over the newly created ones. After that, launch the cluster. >> >> Yeah I know, I do that on a fairly regular basis. The intent was to let >> the OP know that ripping base/ out of cluster and transplanting a new >> one in is not possible. > > OK, lesson learned: Next time I will do a tar backup of PGDATA after > the configuration of the cluster and before creating any database in it. As Paul said this is probably more then you want to do. If for no other reason then that the env(in particular locale) where you untar the PGDATA may be different from where you tar(ed) it. This will leave you with cluster that is out of sync with its environment. > > Thanks > > matthias > -- Adrian Klaver adrian.klaver@aklaver.com
On 2020-05-02 12:23:29 +0200, Matthias Apitz wrote:
> So the question remains: What I have to move by tar to get the cluster
> setup on the new host, but without the databases in it?
What exactly do you mean by "the cluster [setup], but without the
databases in it"? What do you want to preserve that a simple initdb
doesn't recreate? Configuration? Users and passwords? Other stuff?
If you can answer this question, the solution will probably be
simple.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"