Differences

This shows you the differences between two versions of the page.

--- postgresql_backup_recovery_logical [2019/08/01 12:40] – [Appendix] andonovj
+++ postgresql_backup_recovery_logical [2021/06/24 09:51] (current) – andonovj
@@ Line 1: / Line 1: @@
 =====Overview=====
-Logical backup refers to simple export of a data, achieved by the pg_dump. It is important to note that logical backup cannot be used in conjunction of XLOG or other incremental features, but it is very convenient for data migration.
+Logical backup refers to simple export of a data, achieved by the pg_dump. Logical backups are vastly used between different versions. For example transfer of a database between PostgreSQL 9.6 -> 11.
+ It is important to note that logical backup cannot be used in conjunction of XLOG or other incremental features, but it is very convenient for data migration.
+=====Backup=====
+Of course with logical backup we can backup:
+  * Entire Server
+  * Particular Database
+  * All roles and privileges
+====Globals====
+To be sure that there won't be any problems, we can dump all roles within the cluster as follows:
+<Code:bash|Dump all roles>
+-bash-4.2$ pg_dumpall -p 5432 -v --roles-only -f "/var/lib/pgsql/migrationToDev/roles.sql"
+**********************************************************
+</Code>
+This will generates us a file called roles.sql in the mentioned directory which we can execute on the destination server.
+if you want to generate a dump also for the tablespaces use the following:
+<Code:bash|Dump all globas>
+-bash-4.2$ pg_dumpall -p 5432 -v --globals-only -f "/var/lib/pgsql/migrationToDev/globals.sql"
+</Code>
+====Particular Database====
 Logical backup can also be configured in many ways, as simple as to dump a particular database in SQL script file.
 In nutshell there are two things which you should remember:
@@ Line 18: / Line 43: @@
 </sxh>
-Our backup will not be so much readable with text editors:
 <sxh bash>
@@ Line 57: / Line 81: @@
 </sxh>
+====Migrate tables between databases====
+We can migrate tables between databases, given the structure is already there:
+<Code:bash|Migrate tables>
+pg_dump -Fc --data-only --table config --table config_properties wl_sportsbook | pg_restore --data-only -d wl_config
+</Code>
+====Backup all roles and Users====
+To backup users and roles, we have to use a way similar to backing up the entire server, just we will backup only the globals as follows:
+<Code:bash|Dump Roles & Users>
+pg_dumpall -h localhost -p 5432 -v --globals-only > hunter_dev_globals.sql
+</Code>
+This way, it will be pure SQL and can be simply re-run on other server.
 =====Restore=====
 In terms of logical backup, restore and recovery are the same thing. It means, execute the script in order to REDO the changes.
-Simple way will be just to execute the script as follows:
+However BARE IN MIND ONE PROBLEM :)
+  * Backup taken with: **pg_dump: Restore with psql**
 <sxh bash>
@@ Line 70: / Line 110: @@
 -- Dumped from database version 10.0
 -- Dumped by pg_dump version 10.0
-SET statement_timeout = 0;
-SET
-SET lock_timeout = 0;
-SET
-SET idle_in_transaction_session_timeout = 0;
-SET
-SET client_encoding = 'UTF8';
-SET
-SET standard_conforming_strings = on;
-SET
-SET check_function_bodies = false;
-SET
-SET client_min_messages = warning;
-SET
-SET row_security = off;
-SET
 </sxh>
+  * Backup taken with: **pg_dump -Fc: Restore with pg_restore**
+<sxh bash>
+-bash-4.2$ pg_restore -d postgres -C -Fc hunter_dev_backup.sql <- pg_restore will connect to postgres DB, create hunter_dev and switch
+-bash-4.2$ psql
+psql (9.6.14)
+Type "help" for help.
+postgres=# \l
+                                      List of databases
+    Name     |    Owner    | Encoding |   Collate   |    Ctype    |     Access pri
+vileges
+-------------+-------------+----------+-------------+-------------+---------------
+------------
+ hunter_dev  | hunter_dev  | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
+</sxh>
+If you have many databases you can restore them via bash script.
+<sxh bash>
+#!/bin/bash
+for database in `ls`; do
+outfile=importAll.log
+time=`date`
+echo "Started import for $database at $time" >> $outfile
+pg_restore -d postgres -C $database >> $import_$database.log
+time=`date`
+echo "Import for $database done  $time" >> $outfile
+done
+</sxh>
+The following script will restore all databases in the current directory.
 ===== Appendix =====
@@ Line 135: / Line 194: @@
 backup_creati=`find $backup_file_today -maxdepth 1 -type f -print | wc -l`
-#Controllo che il numero di backup creati sia uguale al numero dei database
 if [ $numero_database == $backup_creati ] ; then
-        echo "PROCEDURA BACKUP TERMINATA SENZA ERRORI" >> $backup_file
+        echo "Backup procedure completed without error" >> $backup_file
 else
-        echo "ERRORE NELLA PROCEDURA BACKUP" >> $backup_file
+        echo "Error during backup procedure execution" >> $backup_file
 fi
 </sxh>
@@ Line 170: / Line 228: @@
 pg_restore (options)
--p, –port=PORT database server port number
+-C, --create=Creates the database before restoring into it. With "--clean" it will perform DROP + Create
--i, –ignore-version proceed even when server version mismatches
+-p, --port=PORT database server port number
--h, –host=HOSTNAME database server host or socket directory
+-i, --ignore-version proceed even when server version mismatches
--U, –username=NAME connect as specified database user
+-h, --host=HOSTNAME database server host or socket directory
--W, –password force password prompt (should happen automatically)
+-U, --username=NAME connect as specified database user
--d, –dbname=NAME connect to database name
+-W, --password force password prompt (should happen automatically)
--v, –verbose verbose mode
+-d, --dbname=NAME connect to database name
+-v, --verbose verbose mode
 </sxh>