PostgreSQL Tutorial 7 -- Taking DB Backup and Restoring the DB Backup in postgresql

Below is the process to create Normal DB Backup in postgresql.






Below is the process to create DB Backup (.SQL file) in postgresql.





Below is the process to create TAR Backup in postgresql.




Below is the process to Compressed DB Backup in postgresql.




Below is the process to Restore DB Backup in postgresql.





When we we try to restore we got the error "pg_restore: error: could not execute query: error: schema "public" already exists". We fixed the issue with the command. 

pg_restore -h localhost --schema=public -d ios5 -U postgres -Ft "C:\Users\dDriv\Downloads\Shiftlog221.tar"



Below is the process to Restore TAR DB Backup in postgresql.


1. Creating the DB for Restore


2. Restoring the TAR DB Backup with PG_Restore utility. 






Below is the process to Restore Compressed DB Backup in postgresql.


1. Creating the DB for Restore



2. Restoring the Compressed DB Backup with PG_Restore utility.



Please note that, the tar and compressed backup sizes in above. Compressed backup size is 9 MB and Tar backup is 150 MB. 

Comments

Post a Comment

Popular posts from this blog

How to connect postgresql database through ODBC driver in excel

Image
Postgresql has ODBC driver " psqlodbc " which will be used to connect to postgresql databases. In this post, we are connecting to postgresql from excel using ODBC driver.  Step1: Verifying the existing ODBC driver in windows (both 32 and 64 bit driver) for availability of postgresql driver.  32-bit driver , we noticed that there is no postgresql driver in the drivers list ( Name column values doesn't start with Postgres text). 64-bit driver , we noticed that there is no postgresql driver in the drivers list ( Name column values doesn't start with Postgres text).  Step2: Checking the current postgresql server version, to get the corresponding psqlodbc driver. As per below screen shot, current version is " 10.10 ". i will try to get and install psqlodbc of 10.10 version. Step3: When check the psqlodbc driver ( https://www.postgresql.org/ftp/odbc/versions/msi/ ), i have found the, psqlodbc_10_3 is the last version that was a...
Read more

Postgresql System files and their uses

Image
The postmaster.opts File This postmaster.opts file sets the default invocation options for the postmaster program, which is the main PostgreSQL program. Typically, it will contain the full path to the postmaster program, a -D option to set the full path to the principal data directory, and optionally, a -i flag to enable network connections. The postmaster.opts options are listed in Table 11-5.  Table 11-5. postmaster Options Option   Description  -B          nbufs Sets the number of shared memory buffers to nbufs. -d           level Sets the level of debug information (level should be a number 1 through 5)               written to the server log. -D dir      Sets the database directory (/data) to dir. There is no default value. If no    ...
Read more