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/10/18 20:04] – external edit 127.0.0.1 | 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. | ||
| Line 66: | Line 102: | ||
| * Backup taken with: **pg_dump: Restore with psql** | * Backup taken with: **pg_dump: Restore with psql** | ||
| - | |||
| - | |||
| <sxh bash> | <sxh bash> | ||
| Line 83: | Line 117: | ||
| <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) | ||