Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
| postgresql_backup_recovery_logical [2019/08/01 13:10] – andonovj | postgresql_backup_recovery_logical [2021/06/24 09:51] (current) – andonovj | ||
|---|---|---|---|
| Line 3: | Line 3: | ||
| 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. | 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: | ||
| + | |||
| + | < | ||
| + | -bash-4.2$ pg_dumpall -p 5432 -v --roles-only -f "/ | ||
| + | ********************************************************** | ||
| + | </ | ||
| + | |||
| + | 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: | ||
| + | |||
| + | < | ||
| + | -bash-4.2$ pg_dumpall -p 5432 -v --globals-only -f "/ | ||
| + | </ | ||
| + | |||
| + | |||
| + | ====Particular Database==== | ||
| Logical backup can also be configured in many ways, as simple as to dump a particular database in SQL script file. | 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: | In nutshell there are two things which you should remember: | ||
| Line 19: | Line 43: | ||
| </ | </ | ||
| - | Our backup will not be so much readable with text editors: | ||
| <sxh bash> | <sxh bash> | ||
| Line 58: | Line 81: | ||
| </ | </ | ||
| + | ====Migrate tables between databases==== | ||
| + | We can migrate tables between databases, given the structure is already there: | ||
| + | < | ||
| + | pg_dump -Fc --data-only --table config --table config_properties wl_sportsbook | pg_restore --data-only -d wl_config | ||
| + | </ | ||
| + | ====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: | ||
| + | |||
| + | < | ||
| + | pg_dumpall -h localhost -p 5432 -v --globals-only > hunter_dev_globals.sql | ||
| + | </ | ||
| + | |||
| + | This way, it will be pure SQL and can be simply re-run on other server. | ||
| =====Restore===== | =====Restore===== | ||
| In terms of logical backup, restore and recovery are the same thing. It means, execute the script in order to REDO the changes. | 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: | ||
| - | |||
| - | * Backup taken in plain SQL | ||
| However BARE IN MIND ONE PROBLEM :) | However BARE IN MIND ONE PROBLEM :) | ||
| - | Backup taken with: | + | * Backup taken with: **pg_dump: |
| - | + | ||
| - | pg_dump (WITHOUT -Fc) should be restored via psql: | + | |
| - | In other words: | + | |
| - | + | ||
| - | | + | |
| - | | + | |
| <sxh bash> | <sxh bash> | ||
| Line 86: | Line 112: | ||
| </ | </ | ||
| - | * Backup taken with customer (-Fc) format | ||
| - | If the pg_dump is used with pg_dump -Fc (customer) format. Then a pg_restore should be used: | + | * Backup taken with: **pg_dump -Fc: Restore with pg_restore** |
| <sxh bash> | <sxh bash> | ||
| - | -bash-4.2$ pg_restore -d hunter_dev | + | -bash-4.2$ pg_restore -d postgres |
| -bash-4.2$ psql | -bash-4.2$ psql | ||
| psql (9.6.14) | psql (9.6.14) | ||
| Line 106: | Line 131: | ||
| </ | </ | ||
| + | |||
| + | 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 " | ||
| + | pg_restore -d postgres -C $database >> $import_$database.log | ||
| + | time=`date` | ||
| + | echo " | ||
| + | done | ||
| + | </ | ||
| + | |||
| + | The following script will restore all databases in the current directory. | ||
| ===== Appendix ===== | ===== Appendix ===== | ||
| Line 153: | Line 194: | ||
| backup_creati=`find $backup_file_today -maxdepth 1 -type f -print | wc -l` | 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 | if [ $numero_database == $backup_creati ] ; then | ||
| - | echo "PROCEDURA BACKUP TERMINATA SENZA ERRORI" >> $backup_file | + | echo "Backup procedure completed without error" >> $backup_file |
| else | else | ||
| - | echo "ERRORE NELLA PROCEDURA BACKUP" >> $backup_file | + | echo "Error during backup procedure execution" >> $backup_file |
| fi | fi | ||
| </ | </ | ||