3.7. Backing up and restoring PostgreSQL data with physical copies

Procedure

1. Stop the postgresql service:

   # systemctl stop postgresql.service

2. Create a backup directory, for example:

   # mkdir -p /root/postgresql-backup/

3. Back up the /var/lib/pgsql/ directory:

   # cp -rp /var/lib/pgsql/ /root/postgresql-backup/

   The /var/lib/pgsql/ contains all essential files of the PostgreSQL database server, including configuration files, data files, and logs.

4. Start the postgresql service:

   # systemctl start postgresql.service

Procedure

1. Stop the postgresql service:

   # systemctl stop postgresql.service

2. Remove the current /var/lib/pgsql/ directory:

   # rm -rf /var/lib/pgsql/

3. Restore the data directory from your backup:

   # cp -rp /root/postgresql-backup/pgsql/ /var/lib/

4. Ensure the correct ownership of the /var/lib/pgsql/ directory:

   # chown -R postgres:postgres /var/lib/pgsql/

5. Restore the SELinux context of the /var/lib/pgsql/ directory:

   # restorecon -Rv /var/lib/pgsql/

6. Start the postgresql service:

   # systemctl start postgresql.service

Verification

1. Log in as the postgres user.

2. Connect to a database:

   $ psql <database>

3. Access data in the database:

   <database>=# SELECT * FROM <table>;

4. Disconnect from the PostgreSQL service:

   <database>=# \q