I know how to backup and restore one or more schema(s) from a PostgreSQL database using psql:
- Go to the folder that contains the executables of PostgreSQL:
cd C:\Program Files\PostgreSQL\11\bin
- Connect to the database:
psql -U user -p port -d database
- Rename the schema to lowercases:
ALTER SCHEMA "schema_name"
RENAME TO "lowercase_schema_name";
- Exit PostgreSQL:
- Save the schema(s) using the -n flag:
pg_dump -U user -p port -n schema1 -n schema2 database > "C:\path\to\directory\schema_backup.sql"
- Add the schema(s) to the desired database (the existing schemas are preserved):
psql -U user -p port -d database -f"C:\path\to\directory\schema_backup.sql"
This procedure works well and is quick to perform. However, as I have to ask uninitiated people to do it, I would have liked not to have to go through the command prompt, but only through PgAdmin GUI.
It is very simple in PgAdmin to export and import an entire database by right-clicking on the database and using the backup and restore tools. However, I can't do the same thing just for one particular schema. In the same way as with psql (as described above), I want to save only one (ore more) schema(s) and then import it into a given database. When I save the schema in .sql format, create a new blank schema (for example in the same database) and restore the saved schema, the tables of the new schema are empty...
Any idea how to do this? Maybe I have to change some parameters in Dump options?