{"id":1505,"date":"2022-04-01T16:01:19","date_gmt":"2022-04-01T16:01:19","guid":{"rendered":"https:\/\/visitgis.com\/?p=1505"},"modified":"2022-04-01T16:01:22","modified_gmt":"2022-04-01T16:01:22","slug":"postgres-backup-and-restore-full-guide","status":"publish","type":"post","link":"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/","title":{"rendered":"Postgres Backup and Restore Full Guide"},"content":{"rendered":"\n<p>This tutorial is full guide on how to take postgres backup and restore with examples. Besides, it is providing live exercises for hand on experience. This tutorial covers following topics:<br>1) <a href=\"https:\/\/www.postgresql.org\/\">PostgreSQL<\/a> Logical backup<br>2) PostgreSQL Logical restore<br>3) PostgreSQL Physical Backups<br>4) PostgreSQL Physical Restores<br>5) Lab exercises covering Logical Backup &amp; Restores<br>6) Lab exercise covering Physical Backup &amp; Restores<\/p>\n\n\n\n<p><br><strong><code>This document is straight hit on goal of logical, physical backups and scheduling of jobs.<\/code><\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Pre-Requirements<\/a><\/h2>\n\n\n\n<p>It is being considered below mention technologies install before implementation<br>1. CentOS 7<br>2.<a href=\"https:\/\/visitgis.com\"> Postgresql 11<\/a><br>3. First Restore the provided database.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Logical Backup<\/a><\/h2>\n\n\n\n<p>In Postgres, we have three fundamental approaches to make backup of Database objects<br>1. Logical Backup (SQL Dump)<br>2. Physical Backup (File System level Backup)<br>3. Point in time Recovery (Continues Archiving)<br>In Postgres SQL Dump backup is logical backup and it uses <strong>pg_dump<\/strong> utility for this purpose.The advantage of this backup is, it makes your life easy. Because it is easy to use and it is compatible with upgrading versions of OS and Postgres. But, file system level backups are dependent to Server Version and System architecture.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Dump Method<\/a><\/h2>\n\n\n\n<p>This method is to generate a file with the help of SQL commands and these files can feedback to the server. It will create the database in same state as it was at that time of dump creation. Pg_dump or pg_dumpall are the utilities provided by PostgreSQL fir this purpose.<br><\/p>\n\n\n\n<p><strong>Syntax:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code has-ast-global-color-8-background-color has-text-color has-background\" style=\"color:#40f40b\"><code>Pg_dump Database_name > \u201cPath of Foler\u201d\\Name of file.sql\r\ng_dump dumps a database as a text file or to other formats.\r\n\r\nUsage:\r\n  pg_dump &#91;OPTION]... &#91;DBNAME]\r\n\r\nGeneral options:\r\n  -f, --file=FILENAME          output file or directory name\r\n  -F, --format=c|d|t|p         output file format (custom, directory, tar,\r\n                               plain text (default))\r\n  -j, --jobs=NUM               use this many parallel jobs to dump\r\n  -v, --verbose                verbose mode\r\n  -V, --version                output version information, then exit\r\n  -Z, --compress=0-9           compression level for compressed formats\r\n  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock\r\n  --no-sync                    do not wait for changes to be written safely to disk\r\n  -?, --help                   show this help, then exit\r\n\r\nOptions controlling the output content:\r\n  -a, --data-only              dump only the data, not the schema\r\n  -b, --blobs                  include large objects in dump\r\n  -B, --no-blobs               exclude large objects in dump\r\n  -c, --clean                  clean (drop) database objects before recreating\r\n  -C, --create                 include commands to create database in dump\r\n  -E, --encoding=ENCODING      dump the data in encoding ENCODING\r\n  -n, --schema=SCHEMA          dump the named schema(s) only\r\n  -N, --exclude-schema=SCHEMA  do NOT dump the named schema(s)\r\n  -o, --oids                   include OIDs in dump\r\n  -O, --no-owner               skip restoration of object ownership in\r\n                               plain-text format\r\n  -s, --schema-only            dump only the schema, no data\r\n  -S, --superuser=NAME         superuser user name to use in plain-text format\r\n  -t, --table=TABLE            dump the named table(s) only\r\n  -T, --exclude-table=TABLE    do NOT dump the named table(s)\r\n  -x, --no-privileges          do not dump privileges (grant\/revoke)\r\n  --binary-upgrade             for use by upgrade utilities only\r\n  --column-inserts             dump data as INSERT commands with column names\r\n  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting\r\n  --disable-triggers           disable triggers during data-only restore\r\n  --enable-row-security        enable row security (dump only content user has\r\n                               access to)\r\n  --exclude-table-data=TABLE   do NOT dump data for the named table(s)\r\n  --if-exists                  use IF EXISTS when dropping objects\r\n  --inserts                    dump data as INSERT commands, rather than COPY\r\n  --load-via-partition-root    load partitions via the root table\r\n  --no-comments                do not dump comments\r\n  --no-publications            do not dump publications\r\n  --no-security-labels         do not dump security label assignments\r\n  --no-subscriptions           do not dump subscriptions\r\n  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs\r\n  --no-tablespaces             do not dump tablespace assignments\r\n  --no-unlogged-table-data     do not dump unlogged table data\r\n  --quote-all-identifiers      quote all identifiers, even if not key words\r\n  --section=SECTION            dump named section (pre-data, data, or post-data)\r\n  --serializable-deferrable    wait until the dump can run without anomalies\r\n  --snapshot=SNAPSHOT          use given snapshot for the dump\r\n  --strict-names               require table and\/or schema include patterns to\r\n                               match at least one entity each\r\n  --use-set-session-authorization\r\n                               use SET SESSION AUTHORIZATION commands instead of\r\n                               ALTER OWNER commands to set ownership\r\n\r\nConnection options:\r\n  -d, --dbname=DBNAME      database to dump\r\n  -h, --host=HOSTNAME      database server host or socket directory\r\n  -p, --port=PORT          database server port number\r\n  -U, --username=NAME      connect as specified database user\r\n  -w, --no-password        never prompt for password\r\n  -W, --password           force password prompt (should happen automatically)\r\n  --role=ROLENAME          do SET ROLE before dump\r\n\r\nIf no database name is supplied, then the PGDATABASE environment\r\nvariable value is used.\r\n\r\nReport bugs to &lt;pgsql-bugs@postgresql.org>.\r\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step to take Postgres Backup and  Restore <\/h2>\n\n\n\n<p>Open the terminal and follow these <a href=\"https:\/\/visitgis.com\/\">commands<\/a>.<\/p>\n\n\n\n<pre class=\"wp-block-code has-ast-global-color-8-background-color has-text-color has-background\" style=\"color:#b3f629\"><code>&#91;umair@localhost ~]$ ## connect to postgres super user\r\n&#91;umair@localhost ~]$ ##################################\r\n&#91;umair@localhost ~]$ sudo su - postgres\r\n&#91;sudo] password for umair: \r\nLast login: Tue Jun 23 01:16:02 PKT 2020 on pts\/0\r\n-bash-4.2$ psql\r\npsql (11.8)\r\nType \"help\" for help.\r\n<\/code><\/pre>\n\n\n\n<p>Here we will check the list of all databases already exist in server.<\/p>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>postgres=# ## check how many databases already exist.\r\npostgres-# \\l\r\n<\/code><\/pre>\n\n\n\n<p>Lists of database <\/p>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>List of databases\r\nName    |  Owner   | Encoding |   Collate   |    Ctype    |   Acces\r\ns privileges\r\n-----------+----------+----------+-------------+-------------+--------\r\n---------------\r\ndirdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |\r\nmydb2     | myuser2  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |\r\nnewdb     | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |\r\npostgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |\r\ntemplate0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/post\r\ngres          +\r\n|          |          |             |             | postgre\r\ns=CTc\/postgres\r\ntemplate1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/post\r\ngres          +\r\n|          |          |             |             | postgre\r\ns=CTc\/postgres\r\ntutdb     | postgres | UTF8     | en_US.utf8  | en_US.utf8  |\r\n(7 rows)\r\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Four backup formats<\/a><\/h2>\n\n\n\n<ul><li>*.bak: compressed binary format<\/li><li>*.sql: plaintext dump<\/li><li>*.tar: tarball<\/li><li>Directory Format<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">How to backup only one table?<\/h2>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>-bash-4.2$ ### we will backup in plan text format.\r\n-bash-4.2$ pg_dump -d tutdb -p 5432 -U postgres -t customer -f \/tmp\/tempfile\/custtab_20200623.txt\r\n-bash-4.2$ ### Its created\r\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">How to back up in insert file format?<\/h2>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>-bash-4.2$ #### if you want to restore the plan text for format backup use psql utility\r\n-bash-4.2$ ### now we take backup in inserts format.\r\n-bash-4.2$ pg_dump -d tutdb -p 5432 -U postgres -t customer --inserts -f \/tmp\/tempfile\/custtab_20200623.txt\r\n-bash-4.2$ ## its created please check the directory path.\r\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><a>How to take back up of Entire Database?<\/a><\/h2>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>-bash-4.2$ ###Now we will take the backup of entire database.\r\n-bash-4.2$ pg_dump -d tutdb -p 5432 -U postgres  --inserts -v -f \/tmp\/tempfile\/entiretutdb_inserts_20200623.txt\n\n\n-bash-4.2$ ## you see here we add -v in pervious command. it verbose which shows about the all transection details and show you on screen\r\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><a>How to backup database in custom file format?<\/a><\/h2>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>-bash-4.2$ ###Lets take the backup in custom format. Here we will add -F c and this format is not human readable. This is the only format use for pg_restore for restore of database.\r\n-bash-4.2$ pg_dump -d tutdb -p 5432 -U postgres  -F c -v -f  \/tmp\/tempfile\/entiretutdb_custom_20200623.txt\r\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><a>How to take backup database in directory format?<\/a><\/h2>\n\n\n\n<p>Before entering this command, please make sure particular directory has been created with full permission for user.<\/p>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>-bash-4.2$ ## lets take the backup in diecctory format\r\n-bash-4.2$ ## first create the directory and give permissions for postgres or group users.\r\n-bash-4.2$ ## i have created the directory \/tmp\/tempfile\/bkdir\r\n-bash-4.2$ ##here we will use directory name instead of file name\r\n-bash-4.2$ pg_dump -d tutdb -p 5432 -U postgres  -F d -v -f \/tmp\/tempfile\/bkdir\r\n<\/code><\/pre>\n\n\n\n<p>You can use \u2013j at the end of above command to make threads of backup process and catalyze the process. Here I am creating three threads.<\/p>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>-bash-4.2$ pg_dump -d tutdb -p 5432 -U postgres  -F d -v -f \/tmp\/tempfile\/bkdir -j 3<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">How to restore postgres <a href=\"https:\/\/visitgis.com\">database<\/a>?<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Essential Step before Restore<\/a><\/h3>\n\n\n\n<p>Since the text files generated by pg_dump contain a set of SQL commands, they can fed to the psql utility. The database itself will not create by psql, so you must create it yourself from template0 first. So, the general command form to restore a dump is:<br>createdb -T template0 <strong>database_name<\/strong> psql <strong>database_name<\/strong> &lt; <strong>database.sql<\/strong><br>Before start to restore an SQL dump. The creation of the objects with the original ownership and\/or permissions. it is crucial to make sure that all users who have granted permissions on objects., or who own objects in the uploaded database, already exist. Otherwise, the restoration process will fail.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>How to restore the database from plan text file format?<\/a><\/h2>\n\n\n\n<p>Here we will restore only one table. Please use psql utility for restore of plan text file format backup.<\/p>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>-bash-4.2$ ## first we will restore the plan text format. As we discussed we will use psql.\r\n-bash-4.2$ psql -d tutdb -p 5432 -U postgres -f \/tmp\/tempfile\/custtab_20200623.txt\r\n\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><a>How to create new database and restore existing database in custom format?<\/a><\/h2>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>-bash-4.2$ ### Now we will restore full data . first we drop tutdb and then create again\/\r\n-bash-4.2$ psql\r\npsql (11.8)\r\nType \"help\" for help.\r\npostgres=# drop database tutdb;\r\nDROP DATABASE\r\npostgres=# create database tutdb;\r\nCREATE DATABASE\r\npostgres=# \\l\r\npostgres=# \\q\r\n-bash-4.2$ ### now we restore full database.\r\n-bash-4.2$ ## here i will use custom format.\r\n-bash-4.2$ pg_restore -d tutdb -U postgres -p 5432 \/tmp\/tempfile\/entiretutdb_custom_20200623.txt\r\n-bash-4.2$ ## database populated lets see\r\n-bash-4.2$ psql\r\npsql (11.8)\r\nType \"help\" for help.\r\n\r\npostgres=# \\c tutdb\r\nYou are now connected to database \"tutdb\" as user \"postgres\".\r\ntutdb=# \r\ntutdb=# \\dt\r\ntutdb=# select * from actor;\r\ntutdb=# \\q\r\n-bash-4.2$ ####it perfectly done\r\n-bash-4.2$ #### lets create an other user and try to restore database from directory dump formate.\r\n-bash-4.2$ ### lets do it.\r\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><a>How to restore database from directory format?<\/a><\/h2>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>postgres=# drop database dirdb;\r\nDROP DATABASE\r\npostgres=# create database dirdb;\r\nCREATE DATABASE\r\npostgres=# \\q\r\n-bash-4.2$ pg_restore -d dirdb -U postgres -p 5432 \/tmp\/tempfile\/bkdir\r\n-bash-4.2$ ### its restores lets see\r\n-bash-4.2$ psql\r\npsql (11.8)\r\nType \"help\" for help.\r\n\r\npostgres=# \\c dirdb\r\nYou are now connected to database \"dirdb\" as user \"postgres\".\r\ndirdb=# \\dt\r\n### its perfectly done.\r\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Objects Definitions Backup<\/a><\/h2>\n\n\n\n<p>Use the following command to backup all objects in all databases. <\/p>\n\n\n\n<p>It includes <\/p>\n\n\n\n<p>roles, databases, tablespaces, tables, schemas, indexes, functions, triggers, constraints, privileges, views, and ownerships:<\/p>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>pg_dumpall \u2013U postgres --schema-only > \u201cMyPATH\u201d\\definitions.sql\r\n\r\nUse the following command to backup the role definition only:\r\npg_dumpall \u2013U postgres --roles-only > \u201cMyPATH\u201d\\roles.sql\r\n\r\nUse the following command to backup the tablespaces definition:\r\npg_dumpall \u2013U postgres --tablespaces-only > \u201cMyPATH\u201d\\tablespaces.sql\r\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Physical Backup<\/a><\/h2>\n\n\n\n<p>All physical backups are dependent on source and target architecture.<\/p>\n\n\n\n<p> It also depends on version, compiler flags and paths. Source and target environment must be equal.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>File System Level Backup<\/a><\/h2>\n\n\n\n<pre class=\"wp-block-code has-vivid-green-cyan-color has-ast-global-color-8-background-color has-text-color has-background\"><code>&#91;umair@localhost Documents]$ ### create directory for archive logs\r\n&#91;umair@localhost Documents]$ sudo -H -u postgres mkdir \/var\/lib\/postgresql\/pg_log_archive\r\n&#91;sudo] &#91;umair@localhost Documents]$ ## enable archive logging\r\n&#91;umair@localhost Documents]$ sudo nano \/var\/lib\/pgsql\/11\/data\/postgresql.conf\r\n&#91;umair@localhost Documents]$ systemctl restart postgresql-11\r\n&#91;umair@localhost Documents]$ ## create a database with insert data\r\n&#91;umair@localhost Documents]$ sudo su - postgres\r\nLast login: Thu Jun 25 00:01:24 PKT 2020 on pts\/1\r\n-bash-4.2$ psql -c \"create database testdb\"\r\nCREATE DATABASE\r\npassword for umair:\r\npsql testdb -c \"\r\n> create table posts (\r\n>   id integer,\r\n>   title character varying(100),\r\n>   content text,\r\n>   published_at timestamp without time zone,\r\n>   type character varying(100)\r\n> );\r\n> \r\n> insert into posts (id, title, content, published_at, type) values\r\n> (100, 'Intro to SQL', 'Epic SQL Content', '2018-01-01', 'SQL'),\r\n> (101, 'Intro to PostgreSQL', 'PostgreSQL is awesome!', now(), 'PostgreSQL');\r\n> \"\r\nINSERT 0 2\r\n-bash-4.2$ ## achive the logs\r\n-bash-4.2$ psql -c \"select pg_switch_wal();\"\r\n pg_switch_wal \r\n---------------\r\n 0\/C01E1E0\r\n(1 row) -bash-4.2$ ##backup database\r\n-bash-4.2$ pg_basebackup -Ft -D \/var\/lib\/postgresql\/db_file_backup\r\n-bash-4.2$ ##Stop database\r\n-bash-4.2$ systemctl stop postgresql-11\r\n-bash-4.2$ rm var\/lib\/pgsql\/11\/data\/* -r\r\nrm: cannot remove \u2018var\/lib\/pgsql\/11\/data\/*\u2019: No such file or directory\r\n-bash-4.2$ rm \/var\/lib\/pgsql\/11\/data\/* -r\r\n-bash-4.2$ ls \/var\/lib\/pgsql\/11\/data\/\r\n-bash-4.2$ ls \/var\/lib\/pgsql\/11\r\nbackups  data  initdb.log\r\n-bash-4.2$ ##restore files\r\n-bash-4.2$ tar xvf \/var\/lib\/postgresql\/db_file_backup\/base.tar -C \/var\/lib\/pgsql\/11\/data\/\r\nbackup_label\r\ntablespace_map\r\npg_wal\/\r\n.\/pg_wal\/archive_status\/\r\nglobal\/\r\nglobal\/1262\r\nglobal\/1262_fsm\r\nglobal\/2964\r\nglobal\/1213\r\nglobal\/1213_fsm\r\nglobal\/1136\r\nglobal\/1136_fsm\r\nglobal\/1260\r\nglobal\/1260_fsm\r\nglobal\/1261\r\nglobal\/1214\r\nglobal\/2396\r\nglobal\/6000\r\nglobal\/3592\r\nglobal\/6100\r\nglobal\/2846\r\nglobal\/2847\r\nglobal\/2966\r\nglobal\/2967\r\nglobal\/4060\r\n\u2026\r\n\u2026\r\n\u2026\r\nbash-4.2$ tar xvf \/var\/lib\/postgresql\/db_file_backup\/pg_wal.tar -C \/var\/lib\/pgsql\/11\/data\/\r\n00000001000000000000000E\r\n-bash-4.2$ ##add recovery config\r\n-bash-4.2$ nano \/var\/lib\/pgsql\/11\/data\/postgresql.conf\r\n-bash-4.2$ \r\n-bash-4.2$ \r\n-bash-4.2$ \r\n-bash-4.2$ ###restore_command = 'cp \/var\/lib\/postgresql\/pg_log_archive\/%f %p'\r\n-bash-4.2$ ###restore command added\r\n-bash-4.2$ ##start db\r\n-bash-4.2$ systemctl start postg&#91;umair@localhost Documents]$ sudo su - postgres\r\nLast login: Thu Jun 25 00:01:24 PKT 2020 on pts\/1\r\n-bash-4.2$ psql\r\n-bash-4.2$ # verify restore was successful\r\n\r\n\r\n\r\n\r\n\r\nPoint in time recovery PITR \r\n\r\n&#91;umair@localhost Documents]$ # backup database and gzip\r\n&#91;umair@localhost Documents]$ sudo su - postgres\r\nLast login: Thu Jun 25 00:01:24 PKT 2020 on pts\/1\r\n-bash-4.2$ pg_basebackup -Ft -X none -D - | gzip > \/var\/lib\/postgresql\/db_file_backup.tar.gz\r\n# wait\r\n-bash-4.2$ psql testdb -c \"insert into posts (id, title, content, type) values\r\n> (102, 'Intro to SQL Where Clause', 'Easy as pie!', 'SQL'),\r\n> (103, 'Intro to SQL Order Clause', 'What comes first?', 'SQL');\"\r\n\r\n-bash-4.2$ # archive the logs\r\n-bash-4.2$ psql -c \"select pg_switch_wal();\"\r\n-bash-4.2$ # stop DB and destroy data\r\n-bash-4.2$ systemctl stop postgresql-10\r\n-bash-4.2$ rm \/var\/lib\/pgsql\/11\/data\/* -r \r\n-bash-4.2$ ls \/var\/lib\/postgresql\/10\/main\/\r\n\r\n-bash-4.2$ # restore\r\n-bash-4.2$ tar xvfz \/var\/lib\/postgresql\/db_file_backup.tar.gz -C \/var\/lib\/pgsql\/11\/data\/\r\n-bash-4.2$ # add recovery.conf\r\n-bash-4.2$ nano \/var\/lib\/postgresql\/10\/main\/recovery.conf\r\n\r\n  restore_command = 'cp \/var\/lib\/postgresql\/pg_log_archive\/%f %p'\r\n  recovery_target_time = '2020-06-25 15:20:00 EST'\r\n\r\n-bash-4.2$ # start DB\r\n-bash-4.2$ systemctl start postgresql-11\r\n# verify restore was successful\r\n-bash-4.2$ psql test -c \"select * from posts;\r\n-bash-4.2$ # complete and enable database restore\r\n-bash-4.2$ psql -c \"select pg_wal_replay_resume();\"\r\n<\/code><\/pre>\n\n\n\n<p>You have follow this guide of postgres backup and restore. Now, you should do following expercies.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Lab Exercises<\/a> for postgres backup and restore<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Lab Exercise -1<\/a><\/h3>\n\n\n\n<p>The tutdb database is all setup and as a DBA you need to plan a proper backup strategy and implement it.<\/p>\n\n\n\n<ul><li>As the root user, create a folder \/bkupdir and assign ownership to the tutdb user using the chown utility.<\/li><li>Take a full dataase dump of the tutdb database with the pg_dump utility. The dump should be in plain text format.<\/li><li>Nathe dump file as tutdb_full_Data.sql and store it in the \/bkupdir directory.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Lab Exercise \u2013 2<\/a><\/h2>\n\n\n\n<ol type=\"1\"><li>Take the dump backup of tutdb schema and name the file as as tutdb_schema.sql<\/li><li>Take a data only dump of the tutdb database, disable all triggers for faster restore, use the INSERT command instead of COPY, and name the fie as tutdb_data.sql<\/li><li>Take a full dump of actors table and name the file as tutdb_actors.sql.<\/li><\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Lab Exercise \u2013 3<\/a><\/h2>\n\n\n\n<ol type=\"1\"><li>Take a full database dump of tutdb in compressed format using the pd_dump utility, name the file as tutdb_full.dmp<\/li><li>Take a full database cluster dump using pg_dumpall. Remember pg_dumpall supports only plain text format; name the file tutdb.sql<\/li><\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Lab Exercise \u2013 4<\/a><\/h2>\n\n\n\n<p>In this exercise you will demonstrate your ability to restore a database.<\/p>\n\n\n\n<ol type=\"1\"><li>Drop database tutdb<\/li><li>Create database tutdb with owner superuser<\/li><li>Restore the full dump from tutdb_full.sql and verify all the objects and their ownership.<\/li><li>Drop database tutdb.<\/li><li>Create database tutdb with superuser owner<\/li><li>Restore the full dump from the compressed file tutdb_full_fc.dmp and verify all the objects and their ownership.<\/li><\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Lab Exercise -5<\/a><\/h2>\n\n\n\n<ol type=\"1\"><li>Create a directory \/opt\/arch and give ownership to the superuser.<\/li><li>Configure your cluster to run in archive mode and set the archive log location to be \/opt\/arch.<\/li><li>Take a full online base backup of your cluster in the \/pgbackup directory using the pg_basebackup utility.<\/li><\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Lab Exercise \u2013 6<\/a><\/h2>\n\n\n\n<p>A database cluster can encounter different types of failures. Recover your database from a variety of simulated failures:<\/p>\n\n\n\n<ol type=\"1\"><li>Recover from loss of the postgresql.conf file<\/li><li>Recover fom loss of an actors table data file.<\/li><li>Recover from mistakenly drop table actors.<\/li><\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Lab Exercise \u2013 7<\/a><\/h2>\n\n\n\n<ol type=\"1\"><li>Create new database name demodb and insert few records.<\/li><li>Check database archive log is enabling in postgresql.conf file. If you find not enable take necessary action.<\/li><li>Make file base backup using pg_basebackup and restore<\/li><\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Lab Exercise \u2013 8<\/a><\/h2>\n\n\n\n<ol type=\"1\"><li>Create a new database pitrdb and insert records.<\/li><li>Take backup using pg_basebackup() in .tar.gz format.<\/li><li>Set recovery target time.<\/li><li>Restore these backup and keep database running.<\/li><\/ol>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This tutorial is full guide on how to take postgres backup and restore with examples. Besides, it is providing live exercises for hand on experience. This tutorial covers following topics:1) PostgreSQL Logical backup2) PostgreSQL Logical restore3) PostgreSQL Physical Backups4) PostgreSQL Physical Restores5) Lab exercises covering Logical Backup &amp; Restores6) Lab exercise covering Physical Backup &amp; &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/\"> <span class=\"screen-reader-text\">Postgres Backup and Restore Full Guide<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":1507,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_lmt_disableupdate":"","_lmt_disable":"","site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[139,2,140,58],"tags":[145,142,147,137,136,141,138,148,146],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.6.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Postgres Backup and Restore Full Guide - VGIS<\/title>\n<meta name=\"description\" content=\"This is full guide for Postgres Backup and Restore methods including physical and logical backup. We are providing exercises for experience.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Postgres Backup and Restore Full Guide - VGIS\" \/>\n<meta property=\"og:description\" content=\"This is full guide for Postgres Backup and Restore methods including physical and logical backup. We are providing exercises for experience.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/\" \/>\n<meta property=\"og:site_name\" content=\"VGIS\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/Visitgis\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-04-01T16:01:19+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-04-01T16:01:22+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/visitgis.com\/wp-content\/uploads\/2022\/04\/postgres-backup-and-restore-min-scaled.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"2560\" \/>\n\t<meta property=\"og:image:height\" content=\"1280\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"visitgis\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@visitgis\" \/>\n<meta name=\"twitter:site\" content=\"@visitgis\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"visitgis\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/\"},\"author\":{\"name\":\"visitgis\",\"@id\":\"https:\/\/visitgis.com\/#\/schema\/person\/3d6840c24927b6a1ef00426b8f9b09b9\"},\"headline\":\"Postgres Backup and Restore Full Guide\",\"datePublished\":\"2022-04-01T16:01:19+00:00\",\"dateModified\":\"2022-04-01T16:01:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/\"},\"wordCount\":1008,\"commentCount\":5,\"publisher\":{\"@id\":\"https:\/\/visitgis.com\/#organization\"},\"keywords\":[\"how to backup and restore a table in postgresql\",\"how to backup postgresql database in linux\",\"how to restore backup file in postgresql using pgadmin 4\",\"postgres\",\"postgres backup and restore\",\"postgres backup command\",\"postgresql\",\"postgresql backup script\",\"use the pg_restore comman-line cient to...\"],\"articleSection\":[\"Database\",\"GIS\",\"Software\",\"Tutorials\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/\",\"url\":\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/\",\"name\":\"Postgres Backup and Restore Full Guide - VGIS\",\"isPartOf\":{\"@id\":\"https:\/\/visitgis.com\/#website\"},\"datePublished\":\"2022-04-01T16:01:19+00:00\",\"dateModified\":\"2022-04-01T16:01:22+00:00\",\"description\":\"This is full guide for Postgres Backup and Restore methods including physical and logical backup. We are providing exercises for experience.\",\"breadcrumb\":{\"@id\":\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/visitgis.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Postgres Backup and Restore Full Guide\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/visitgis.com\/#website\",\"url\":\"https:\/\/visitgis.com\/\",\"name\":\"VGIS\",\"description\":\"Imagine Here\",\"publisher\":{\"@id\":\"https:\/\/visitgis.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/visitgis.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/visitgis.com\/#organization\",\"name\":\"Visitgis Technologies\",\"url\":\"https:\/\/visitgis.com\/\",\"sameAs\":[\"https:\/\/www.facebook.com\/Visitgis\/\",\"https:\/\/twitter.com\/visitgis\"],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/visitgis.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/visitgis.com\/wp-content\/uploads\/2021\/11\/cropped-Capture-2-1.png\",\"contentUrl\":\"https:\/\/visitgis.com\/wp-content\/uploads\/2021\/11\/cropped-Capture-2-1.png\",\"width\":74,\"height\":38,\"caption\":\"Visitgis Technologies\"},\"image\":{\"@id\":\"https:\/\/visitgis.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/visitgis.com\/#\/schema\/person\/3d6840c24927b6a1ef00426b8f9b09b9\",\"name\":\"visitgis\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/visitgis.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d5610d1cb9e5ef75d67f10e2cf108385?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d5610d1cb9e5ef75d67f10e2cf108385?s=96&d=identicon&r=g\",\"caption\":\"visitgis\"},\"sameAs\":[\"http:\/\/192.248.191.224\"],\"url\":\"https:\/\/visitgis.com\/author\/visitgis\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Postgres Backup and Restore Full Guide - VGIS","description":"This is full guide for Postgres Backup and Restore methods including physical and logical backup. We are providing exercises for experience.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/","og_locale":"en_US","og_type":"article","og_title":"Postgres Backup and Restore Full Guide - VGIS","og_description":"This is full guide for Postgres Backup and Restore methods including physical and logical backup. We are providing exercises for experience.","og_url":"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/","og_site_name":"VGIS","article_publisher":"https:\/\/www.facebook.com\/Visitgis\/","article_published_time":"2022-04-01T16:01:19+00:00","article_modified_time":"2022-04-01T16:01:22+00:00","og_image":[{"width":2560,"height":1280,"url":"https:\/\/visitgis.com\/wp-content\/uploads\/2022\/04\/postgres-backup-and-restore-min-scaled.jpg","type":"image\/jpeg"}],"author":"visitgis","twitter_card":"summary_large_image","twitter_creator":"@visitgis","twitter_site":"@visitgis","twitter_misc":{"Written by":"visitgis","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/#article","isPartOf":{"@id":"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/"},"author":{"name":"visitgis","@id":"https:\/\/visitgis.com\/#\/schema\/person\/3d6840c24927b6a1ef00426b8f9b09b9"},"headline":"Postgres Backup and Restore Full Guide","datePublished":"2022-04-01T16:01:19+00:00","dateModified":"2022-04-01T16:01:22+00:00","mainEntityOfPage":{"@id":"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/"},"wordCount":1008,"commentCount":5,"publisher":{"@id":"https:\/\/visitgis.com\/#organization"},"keywords":["how to backup and restore a table in postgresql","how to backup postgresql database in linux","how to restore backup file in postgresql using pgadmin 4","postgres","postgres backup and restore","postgres backup command","postgresql","postgresql backup script","use the pg_restore comman-line cient to..."],"articleSection":["Database","GIS","Software","Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/","url":"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/","name":"Postgres Backup and Restore Full Guide - VGIS","isPartOf":{"@id":"https:\/\/visitgis.com\/#website"},"datePublished":"2022-04-01T16:01:19+00:00","dateModified":"2022-04-01T16:01:22+00:00","description":"This is full guide for Postgres Backup and Restore methods including physical and logical backup. We are providing exercises for experience.","breadcrumb":{"@id":"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/visitgis.com\/postgres-backup-and-restore-full-guide\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/visitgis.com\/"},{"@type":"ListItem","position":2,"name":"Postgres Backup and Restore Full Guide"}]},{"@type":"WebSite","@id":"https:\/\/visitgis.com\/#website","url":"https:\/\/visitgis.com\/","name":"VGIS","description":"Imagine Here","publisher":{"@id":"https:\/\/visitgis.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/visitgis.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/visitgis.com\/#organization","name":"Visitgis Technologies","url":"https:\/\/visitgis.com\/","sameAs":["https:\/\/www.facebook.com\/Visitgis\/","https:\/\/twitter.com\/visitgis"],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/visitgis.com\/#\/schema\/logo\/image\/","url":"https:\/\/visitgis.com\/wp-content\/uploads\/2021\/11\/cropped-Capture-2-1.png","contentUrl":"https:\/\/visitgis.com\/wp-content\/uploads\/2021\/11\/cropped-Capture-2-1.png","width":74,"height":38,"caption":"Visitgis Technologies"},"image":{"@id":"https:\/\/visitgis.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/visitgis.com\/#\/schema\/person\/3d6840c24927b6a1ef00426b8f9b09b9","name":"visitgis","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/visitgis.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d5610d1cb9e5ef75d67f10e2cf108385?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d5610d1cb9e5ef75d67f10e2cf108385?s=96&d=identicon&r=g","caption":"visitgis"},"sameAs":["http:\/\/192.248.191.224"],"url":"https:\/\/visitgis.com\/author\/visitgis\/"}]}},"modified_by":"visitgis","jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/visitgis.com\/wp-content\/uploads\/2022\/04\/postgres-backup-and-restore-min-scaled.jpg?fit=2560%2C1280&ssl=1","_links":{"self":[{"href":"https:\/\/visitgis.com\/wp-json\/wp\/v2\/posts\/1505"}],"collection":[{"href":"https:\/\/visitgis.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/visitgis.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/visitgis.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/visitgis.com\/wp-json\/wp\/v2\/comments?post=1505"}],"version-history":[{"count":3,"href":"https:\/\/visitgis.com\/wp-json\/wp\/v2\/posts\/1505\/revisions"}],"predecessor-version":[{"id":1509,"href":"https:\/\/visitgis.com\/wp-json\/wp\/v2\/posts\/1505\/revisions\/1509"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/visitgis.com\/wp-json\/wp\/v2\/media\/1507"}],"wp:attachment":[{"href":"https:\/\/visitgis.com\/wp-json\/wp\/v2\/media?parent=1505"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/visitgis.com\/wp-json\/wp\/v2\/categories?post=1505"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/visitgis.com\/wp-json\/wp\/v2\/tags?post=1505"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}