Typically when restoring a multi-schema database from a backup file, you could need to exclude a number of schemas, for one cause or the opposite.
For instance, in case you are restoring an enormous database for improvement functions, you could not need to restore a schema that you recognize has a lot of information, which could trigger the restoration course of to take so lengthy.
pg_restore the command-line utility permits for excluding a number of schemas when restoring a database from a backup file created by pg_dump. You should use its -N or --exclude-schema choice to revive objects within the named schema.
PostgreSQL helps numerous helpful database backup and restores options. On this information, we’ll present find out how to exclude a schema whereas restoring a PostgreSQL multi-schema database from a backup file.
Exclude a Schema Whereas Restoring a PostgreSQL Database
Earlier than you begin the restoration course of, in case you’re performing it on a brand new server, guarantee that the proprietor or consumer of the database as outlined within the backup is created on the server:
$ pg_restore -d myappdb -N schema_name myappdb.dump OR $ pg_restore -d myappdb --exclude-schema=schema_name myappdb.dump
Within the command above, the flag:
-d– is used to specify the goal database identify.-N– specifies the identify of the schema to exclude in the course of the restoration course of.myappdb.dump– is the database backup file identify. Be aware that the file needs to be in one of many non-plain-text codecs as created by pg_dump.
To exclude a number of schemas, use a number of -N flags as proven.
$ pg_restore -d myappdb -N schema_name1 -N schema_name2 -N schema_name3 myappdb.dump OR $ pg_restore -d myappdb --exclude-schema=schema_name1 --exclude-schema=schema_name2 --exclude-schema=schema_name3 myappdb.dump
You possibly can inform pg_restore to create the database specified by the -d choice if it doesn’t exist, through the use of the -C or --create swap as follows:
$ pg_restore -d myappdb -C -N schema_name myappdb.dump OR $ pg_restore -d myappdb --create -N schema_name myappdb.dump
You must be aware that in case the -C swap is used, as within the earlier command, the database identify myappdb specified utilizing the -d swap is barely employed to run the preliminary “DROP DATABASE myappdb” and “CREATE DATABASE myappdb” instructions, all the information is restored into the database identify that exists within the backup file.
To drop or clear and recreate the goal database earlier than connecting to it, use the --clean choice as proven.
$ pg_restore --clean -d testdb -n schema_name myappdb.dump
By default, if there may be an error encountered whereas working the SQL command in the course of the restoration course of, pg_restore will proceed with the method and easily show the errors.
You possibly can let pg-restore terminate when an error is encountered by including the -e or --exit-on-error choice:
$ pg_restore -e -d testdb -n schema_name myappdb.dump OR $ pg_restore --exit-on-error -d testdb -n schema_name myappdb.dump
To make the restoration course of sooner, you should use the -j or --number-of-jobs to run concurrently. This feature ensures that steps similar to creating indexes, creating constraints, or loading information might be executed concurrently utilizing concurrent periods of as much as the required variety of jobs.
Nevertheless, it extremely relies on the variety of CPUs core and disk configuration on the consumer, server, and community:
$ pg_restore -j 4 --clean -d testdb -n schema_name myappdb.dump OR $ pg_restore --number-of-jobs=4 --clean -d testdb -n schema_name myappdb.dump
You possibly can learn extra about pg_restore by viewing its man web page as proven.
$ man pg_restore
That’s all we had for you on this quick information. When you’ve got any feedback or questions, use the suggestions kind under to achieve us.
If You Admire What We Do Right here On TecMint, You Ought to Think about:
TecMint is the quickest rising and most trusted group web site for any type of Linux Articles, Guides and Books on the internet. Hundreds of thousands of individuals go to TecMint! to go looking or browse the 1000’s of revealed articles accessible FREELY to all.
In the event you like what you’re studying, please think about shopping for us a espresso ( or 2 ) as a token of appreciation.
We’re grateful to your by no means ending assist.
