Add option to Logical copy multiple or all databases
Goal
Add support for copying multiple or all databases for the logical method.
TODO / How to implement
1: multiple databases
Add to configuration the option to specify the database list that must be copied. LogicalRestore
restores all existing dump files.
spec:
logicalDump:
options:
dbname:
- "database1"
- "database2"
- "databaseN"
logicalRestore:
options:
Use pg_dump and pg_restore in a loop for each database.
2: all databases
spec:
logicalDump:
options:
logicalRestore:
options:
method 1 (preferable):
Get a database list (except templates)
select datname from pg_catalog.pg_database where not datistemplate;
for Azure Database (exclude 'azure_maintenance','azure_sys')
if retrieval.spec.logicalDump.options.source.type: azure
select datname from pg_catalog.pg_database where not datistemplate and datname not in ('azure_maintenance','azure_sys');
Use pg_dump and pg_restore in a loop for each database.
or
method 2:
Use pg_dumpall for copy all databases and psql for restore.
for source.type: rds
add --no-role-passwords
pg_dumpall --host <HOST> --port <PORT> --username <USERNAMRE> --no-role-passwords --file <DUMPLOCATION>
--no-role-passwords
Do not dump passwords for roles. When restored, roles will have a null password, and password authentication will always fail until the password is set. Since password values aren't needed when this option is specified, the role information is read from the catalog view pg_roles instead of pg_authid. Therefore, this option also helps if access to pg_authid is restricted by some security policy.
for source.type: azure
add --exclude-database
pg_dumpall --host <HOST> --port <PORT> --username <USERNAMRE> --no-role-passwords --exclude-database="azure_maintenance" --exclude-database="azure_sys" --file <DUMPLOCATION>
--exclude-database=pattern
Do not dump databases whose name matches pattern. Multiple patterns can be excluded by writing multiple --exclude-database switches. The pattern parameter is interpreted as a pattern according to the same rules used by psql's \d commands (see Patterns below), so multiple databases can also be excluded by writing wildcard characters in the pattern. When using wildcards, be careful to quote the pattern if needed to prevent shell wildcard expansion.
Restore:
psql --username postgres --dbname postgres --file <DUMPLOCATION>
Acceptance criteria
We can copy one or multiple/all databases from the source postgres instance.