Postgresql Move Database to Another Filesystem

Table of Contents

Mainly used this on SBC (BeagleBone) computer to move a database from SD card (/var/lib...) to SSD disk (/data/lib...). A database will burn up an SD card quickly 😟

List clusters on host

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

Show Data_Dictionary

$ sudo -u postgres psql
psql (11.7 (Debian 11.7-0+deb10u1))
Type "help" for help.

postgres=# SHOW data_directory;
       data_directory       
-----------------------------
 /var/lib/postgresql/11/main
(1 row)
postgres=# \q

Stop Cluster

If you have multiple versions you may have to use the version specific name, example: postgresql@11-main. 

$ sudo systemctl stop postgresql
$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Sat 2020-08-22 13:14:11 EDT; 9s ago
  Process: 23216 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 23216 (code=exited, status=0/SUCCESS)

Aug 22 10:29:07 app2 systemd[1]: Starting PostgreSQL RDBMS...
Aug 22 10:29:07 app2 systemd[1]: Started PostgreSQL RDBMS.
Aug 22 13:14:11 app2 systemd[1]: postgresql.service: Succeeded.
Aug 22 13:14:11 app2 systemd[1]: Stopped PostgreSQL RDBMS.

Move Database Files

$ sudo mkdir -p /data/lib/postgresql/11/main/
$ sudo chown postgres:postgres /data/lib/postgresql/11/main/
$ sudo rsync -av /var/lib/postgresql /data/lib
sending incremental file list
postgresql/
postgresql/.bash_history
...

postgresql/11/main/pg_wal/000000010000000000000005
postgresql/11/main/pg_wal/000000010000000000000006
postgresql/11/main/pg_wal/archive_status/
postgresql/11/main/pg_xact/
postgresql/11/main/pg_xact/0000

sent 74,675,872 bytes  received 25,503 bytes  8,788,397.06 bytes/sec
total size is 74,581,282  speedup is 1.00

Save Old Database Directory

$ sudo mv /var/lib/postgresql/11/main /var/lib/postgresql/11/main.bak

Edit Configuration to Point to New Directory

Configurations are in /etc/postgresql/<version>/main/ directory.

$ sudo vi /etc/postgresql/11/main/postgresql.conf

~

data_directory = '/data/lib/postgresql/11/main'        # use data in another directory

~

Double Check New Directory

$ sudo ls /data/lib/postgresql/11/main
base    pg_commit_ts  pg_logical    pg_notify     pg_serial     pg_stat        pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf
global    pg_dynshmem   pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc     PG_VERSION   pg_xact  postmaster.opts

Start Database Cluster Using New Directory

$ sudo systemctl start postgresql
$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Sat 2020-08-22 13:25:15 EDT; 4s ago
  Process: 24284 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 24284 (code=exited, status=0/SUCCESS)

Aug 22 13:25:15 app2 systemd[1]: Starting PostgreSQL RDBMS...
Aug 22 13:25:15 app2 systemd[1]: Started PostgreSQL RDBMS.

Verify Database

Should show '/data/...'

$ sudo -u postgres psql
psql (11.7 (Debian 11.7-0+deb10u1))
Type "help" for help.

postgres=# SHOW data_directory;
       data_directory       
-----------------------------
 /data/lib/postgresql/11/main
(1 row)
postgres=# \q

Cleanup Old Database Files

Make sure to test things first, and don't forget to free up some disk space for the new database.

$ sudo rm -Rf /var/lib/postgresql/11/main.bak
$ sudo systemctl restart postgresql
$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Sat 2020-08-22 13:28:14 EDT; 7s ago
  Process: 24349 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 24349 (code=exited, status=0/SUCCESS)

Aug 22 13:28:14 app2 systemd[1]: Starting PostgreSQL RDBMS...
Aug 22 13:28:14 app2 systemd[1]: Started PostgreSQL RDBMS.

Re-Create Cluster

If you make a mistake the cluster can be deleted, then re-created. No Problem.

$ sudo rm -rf /mnt/raid1/postgresql/13
$ sudo -u postgres pg_createcluster -d /mnt/raid1/postgresql 13 main

$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory        Log file
13  main    5432 down   postgres /mnt/raid1/postgresql /var/log/postgresql/postgresql-13-main.log

$ sudo pg_ctlcluster  13 main start

$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory        Log file
13  main    5432 online postgres /mnt/raid1/postgresql /var/log/postgresql/postgresql-13-main.log

Continue

Now that you have moved that busy data to a suitable disk, consider setting up a cloud server to use that speedy database.

Join again soon on the next episode of Linux in the Home.

Proceed in the order presented, some things are depending on prior setups.

Book Last Updated: 29-March-2024


PostgreSQL Move - Linux in the House - https://linux-in-the-house.org Creative Commons License