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 [2020/06/18 08:03] – andonovj | postgresql_backup_recovery_logical [2021/06/24 09:51] (current) – andonovj | ||
|---|---|---|---|
| Line 9: | Line 9: | ||
| * Particular Database | * Particular Database | ||
| * All roles and privileges | * 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==== | ====Particular Database==== | ||
| Line 64: | Line 80: | ||
| drwxr-xr-x. 3 postgres postgres 4096 Mar 20 10:05 .. | drwxr-xr-x. 3 postgres postgres 4096 Mar 20 10:05 .. | ||
| </ | </ | ||
| + | |||
| + | ====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==== | ====Backup all roles and Users==== | ||
| Line 72: | Line 95: | ||
| </ | </ | ||
| + | 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. | ||