![]() You might wish to run psql with the ON_ERROR_STOP variable set to alter that behavior and have psql exit with an exit status of 3 if an SQL error occurs: (Sometimes this is what you want, but usually it is not.)īy default, the psql script will continue to execute after an SQL error is encountered. If they do not, the restore will fail to recreate the objects with the original ownership and/or permissions. Non-text file dumps are restored using the pg_restore utility.īefore restoring an SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. ![]() See the psql reference page for more information. psql supports options similar to pg_dump for specifying the database server to connect to and the user name to use. The database dbname will not be created by this command, so you must create it yourself from template0 before executing psql (e.g., with createdb -T template0 dbname). Where dumpfile is the file output by the pg_dump command. The general command form to restore a dump is Text files created by pg_dump are intended to be read in by the psql program. ![]() (Exceptions are those operations that need to operate with an exclusive lock, such as most forms of ALTER TABLE.) pg_dump does not block other operations on the database while it is working. pg_dump is also the only method that will work when transferring a database to a different machine architecture, such as going from a 32-bit to a 64-bit server.ĭumps created by pg_dump are internally consistent, meaning, the dump represents a snapshot of the database at the time pg_dump began running. Remember that pg_dump connections are subject to the normal client authentication mechanisms (which are described in Chapter 21).Īn important advantage of pg_dump over the other backup methods described later is that pg_dump's output can generally be re-loaded into newer versions of PostgreSQL, whereas file-level backups and continuous archiving are both extremely server-version-specific. To override this, either specify the -U option or set the environment variable PGUSER. Like any other PostgreSQL client application, pg_dump will by default connect with the database user name that is equal to the current operating system user name. (Conveniently, the server will normally have the same compiled-in default.) Similarly, the default port is indicated by the PGPORT environment variable or, failing that, by the compiled-in default. The default host is the local host or whatever your PGHOST environment variable specifies. To specify which database server pg_dump should contact, use the command line options -h host and -p port. (If you do not have sufficient privileges to back up the entire database, you can still back up portions of the database to which you do have access using options such as -n schema or -t table.) In particular, it must have read access to all tables that you want to back up, so in order to back up the entire database you almost always have to run it as a database superuser. But remember that pg_dump does not operate with special permissions. This means that you can perform this backup procedure from any remote host that has access to the database. Pg_dump is a regular PostgreSQL client application (albeit a particularly clever one). ![]() While the above command creates a text file, pg_dump can create files in other formats that allow for parallelism and more fine-grained control of object restoration. We will see below how this can be useful. ![]() The basic usage of this command is:Īs you see, pg_dump writes its result to the standard output. PostgreSQL provides the utility program pg_dump for this purpose. The idea behind this dump method is to generate a file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |