1

Hi Im running on a centos 7 machine, Im in the process of migrating an old postgres database to the new server, it has like 10TB of data so Im trying it with pg_basebackup. The new server has an independent disk with enough storage to handle the operation, but it's not the primary disk. I would like to set the PGDATA into this big data disk.

I have followed this steps:

  1. Install postgres-12 and postgres12-contrib

  2. Mount the data-disk into the new machine

  3. Create an empty pgdata directory in the data disk. All with user postgres.

  4. Use /usr/bin/pg_basebackup -h IP_OF_OLD_MACHINE -D /mnt/disks/data-disk/pgdata -P -U USERNAME --wal-method=stream

I think pg_basebackup ended successfully, data it took several hours but finished without prompting any error at all.

total 132K
-rw-------. 1 postgres postgres  230 Mar 18 12:36 backup_label
drwx------. 7 postgres postgres 4.0K Mar 18 12:43 base
-rw-------. 1 postgres postgres   30 Mar 19 00:13 current_logfiles
drwx------. 2 postgres postgres 4.0K Mar 19 00:13 global
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 log
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_commit_ts
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_dynshmem
-rw-------. 1 postgres postgres 4.8K Mar 18 12:36 pg_hba.conf
-rw-------. 1 postgres postgres 4.7K Mar 18 12:36 pg_hba.conf~
-rw-------. 1 postgres postgres 1.6K Mar 19 00:13 pg_ident.conf
drwx------. 4 postgres postgres 4.0K Mar 18 12:36 pg_logical
drwx------. 4 postgres postgres 4.0K Mar 18 12:36 pg_multixact
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_notify
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_replslot
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_serial
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_snapshots
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_stat
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_stat_tmp
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_subtrans
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_tblspc
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_twophase
-rw-------. 1 postgres postgres    3 Mar 19 08:27 PG_VERSION
drwx------. 3 postgres postgres 4.0K Mar 18 12:36 pg_wal
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_xact
-rw-------. 1 postgres postgres   88 Mar 18 12:36 postgresql.auto.conf
-rw-------. 1 postgres postgres  24K Mar 19 08:39 postgresql.conf

Ok, now im trying to initialize a postgres server with this data, the one i've just brought.

This are my attempts, looking the documentation:

/usr/pgsql-12/bin/postgresql-12-setup initdb -D /mnt/disks/data-disk/pgdata
systemctl: invalid option -- 'D'
failed to find PGDATA setting in -D.service


/usr/pgsql-12/bin/postgresql-12-setup initdb --pgdata=/mnt/disks/data-disk/pgdata
systemctl: unrecognized option '--pgdata=/mnt/disks/data-disk/pgdata.service'
failed to find PGDATA setting in --pgdata=/mnt/disks/data-disk/pgdata.service

Is there some mistake in the process?

Update: As @pifor told me i added the PATH to the postgres bash_profile and then started to recognize PG_DATA. The problem now is that initdb aims to find an empty folder, and its not empty due to the pg_basebackup

  • postgresql-12-setup is coded to store PGDATA in some hard coded location in /var/lib/xxx. In your case I would only run initdb --pgdata=/mnt/disks/data-disk/pgdata – pifor Mar 19 at 10:57
  • Hi @pifor , i have tried but -bash: initdb: command not found any clue? – Imanol Y. Mar 19 at 11:05
  • Are you running initdb as postgres user ? Add PATH=/usr/pgsql-12/bin:$PATH in postgres .bash_profile if needed. – pifor Mar 19 at 11:07
  • 2
    I don't think you can run initdb on a non empty PGDATA directory: initdb is designed to create a new database instance from scratch. – pifor Mar 19 at 11:21
  • 1
    Try to 1. modify postgresql.conf to adapt data_directory for new PGDATA. 2. start PG instance (without running initdb) using recovery.signal file (just create an empty file). These 2 steps should allow to restore/recover the backup and start a new database instance using this backup (no need to run initdb). – pifor Mar 19 at 11:58
1

A physical backup (like pg_basebackup) results in an already initialized directory. You don't run initdb. You just start it up, possibly after changing the config files in whatever way is appropriate for your situation.

You probably should have configured this for streaming though with a named slot though, or it will have fallen behind while the backup was being taken, but that would be a different question.

Why not just move the drive array from one server to the other? Is the desire to change the storage hardware as well as the server hardware?

| improve this answer | |
  • The migration is into a third party provider, so new infrastructure is needed. – Imanol Y. Mar 19 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.