In this article, we will see how to download and restore PostgreSQL sample database. We can restore sample database in PostgreSQL server using following methods.
1. Using psql tool on windows.
2. Using Terminal on Linux.
First we have to download PostgreSQL sample database. Go through Download PostgreSQL Sample database
Continue reading How to download and restore PostgreSQL sample database
In this article, we will see how to download and restore PostgreSQL sample database in PostgreSQL server. We can use PostgreSQL sample database for learning and practice PostgreSQL. We will use the DVD rental database for demonstrating the features of PostgreSQL.
The DVD rental database has many objects including tables, views, trigger, functions, sequences and domain.
In this article, we will see how to do schema only backup and restore in PostgreSQL.
Schema only backup contains only objects definition like table definition.
To take a schema level backup in PostgreSQL database and restore on the another database, we use pg_dump utility with the option –schema-only or -s. If we gave –schema-only means, it dumps only the object definitions (schema), not data.
Continue reading How to do schema only backup and restore in PostgreSQL?
In this article, we are going to how to take backup and restore table in PostgreSQL. We use pg_dump to take backup of table wiht –table option and psql or pg_restore tools are used to restore the table using existing backup.
1. Syntax to backup table data:
pg_dump -Fc --data-only -h
-p -U -W -d -t > /path_and_filename_forbackup
Continue reading How to take backup and restore table in PostgreSQL
In this article, we will see how to take backup of a table(s) in PostgreSQL. We can take the backup of single or multiple tables using pg_dump command with –table or -t option.
Syntax to take backup of table(s) in PostgreSQL:
Following syntax is used to take backup of table with table definition:
pg_dump -d databasename --table=yourTable --column-inserts > file_name.sql
Continue reading How to take backup of a table(s) in PostgreSQL
In this article, we will see how to perform custom format backup and restore of PostgreSQL database.
While plain text format produced by pg_dump is natural and simple, at the same time, it is not very flexible because it can create huge output files.
PostgreSQL has a nice little feature that allows users to export the so-called “custom format”, a format that is archived by default.
Continue reading How to perform custom format backup and restore of PostgreSQL database
In this article, we will see how to take backup and restore PostgreSQL table.
It is administrator regular activity to take backup and restore PostgreSQL table from production server to development or UAT servers.
1. pg_dump: Extract a PostgreSQL table into a script file or other archive file.
2. psql: PostgreSQL interactive terminal used to load backup taken using pg_dump.
3. scp is used to copy backup of production to development server.
Note: pg_dump,psql, scp needs to be executed from Linux shell. Not from psql tool.
On Source PostgreSQL Server:
1. Take backup of table using pg_dump.
2. SCP the backup file from source to target(copy backup file from source to target)
On Target PostgreSQL Server:
3. Restore the table using psql tool
4. Verify the table size with production
Here, I am going to take backup of the table ‘student’ from the database ‘r2schools’ on source PostgreSQL server.
pg_dump -d r2schools -t student > /opt/PGBACKUPS/student_table.sql
scp -p student_table.sql root@mongodb2:/home/r2schools/pgbackups
Connect to the target PostgreSQL server and navigate to backup file scp location and verify production backup copied or not.
postgres@mongodb2:/home/r2schools/pgbackups$ pwd /home/r2schools/pgbackups postgres@mongodb2:/home/r2schools/pgbackups$ ls student_table.sql
Now perform restore operation of table ‘student’ on target server using psql command. Here my target server hostname is ‘mongodb2’.
psql -d r2schools < student_table.sql
Where -d is the database in which table exists.
Connect to the r2schools database and verify the total row count of table on source and target PostgreSQL servers.
On Source:
On Target:
Both source and target servers contains same number of rows.
So in this article, we have successfully performed backup and restore of PostgreSQL table.
In this tutorial, we will see how to take backup and restore a PostgreSQL Database.
It is administrator regular activity to take backup and restore a database from production server to development or UAT servers.
1. pg_dump: Extract a PostgreSQL database into a script file or other archive file.
2. pg_dumpall: Extract a PostgreSQL database cluster into a script file.
3. psql: PostgreSQL interactive terminal used to load backup taken using pg_dump.
4. pg_restore: restore a PostgreSQL database from an archive file created by pg_dump.
Note: pg_dump,psql, pg_restore and pg_dumpall needs to be executed from Linux shell. Not from psql tool.
On Source PostgreSQL Server:
1. Take backup of database using pg_dump.
2. SCP the backup file from source to target(copy backup file from source to target)
On Target PostgreSQL Server:
3. Take backup privileges on target server using pg_dumpall
4. Restore the database using psql or pg_restore command.
5. Now, restore the privileges(roles) taken in the step 3.
6. Test the connection to the loaded database.
In this tutorial, i am going to take backup of r2schools from the source PostgreSQL server ‘mongodb1’ and restore the r2schools database into target PostgreSQL server ‘mongodb2’.
1. Create a directory to to store backup files
Create a directory to store backup files. I am creating directory with the name PGBACKUPS in /opt/ path. You create backup directory where you have sufficient space to store backup of database.
postgres@mongodb1:/opt$ mkdir PGBACKUPS postgres@mongodb1:/opt$ cd PGBACKUPS/
2. Now, run the pg_dump command as shown below.
p pg_dump -U postgres -W -d r2schools > /opt/PGBACKUPS/r2schools$(date +%Y-%m-%d_%H_%M_%S).sql
Where
-U postgres–> User name to connect as.
-d r2schools –> Database to take backup.
-W –> Password to be provided for the user.
$(date +%Y-%m-%d_%H_%M_%S) — Current date in YYYY-mm-dd_H_M_S format.
3. Once execute it will ask us the password. Provide the password for the user. Then, backup file created at specified location. In my backup file created at /opt/PGBACKUPS. Lets verify.
postgres@mongodb1:/opt/PGBACKUPS$ pg_dump -U postgres -W -d r2schools > /opt/PGBACKUPS/r2schools$(date +%Y-%m-%d_%H_%M_%S).sql Password: postgres@mongodb1:/opt/PGBACKUPS$ ls r2schools2020-01-07_10_39_00.sql postgres@mongodb1:/opt/PGBACKUPS$
If source and target servers are same, then skip this step.
postgres@mongodb1:/opt/PGBACKUPS$ scp -p r2schools2020-01-07_10_39_00.sql root@mongodb2:/home/r2schools/backups/ root@mongodb2's password: r2schools2020-01-07_10_39_00.sql 100% 16KB 7.7MB/s 00:00 postgres@mongodb1:/opt/PGBACKUPS$
Where
r2schools2020-01-07_10_39_00.sql is the backup file.
root@mongodb2:/home/r2schools/backups/ is the username@hostname:target path to copy backup files.
On Target PostgreSQL Server:
Connect to Target server. Verify the backup files moved to successfully or not.
root@mongodb2:/home/r2schools/backups# ls r2schools2020-01-07_10_39_00.sql root@mongodb2:/home/r2schools/backups# pwd /home/r2schools/backups
1. Take backup roles on the target server using pg_dumpall. Here my target server is mongodb2. Execute the below command from Linux host.
pg_dumpall -r > roles$(date +%Y-%m-%d_%H_%M_%S).sql
Where
-r means take only backup of roles.
1. Load the database r2schools on target server using either psql or pg_restore command.
psql -d r2schools < r2schools2020-01-07_10_39_00.sql
Output of above command:
postgres@mongodb2:/home/r2schools/backups$ psql -d r2schools < r2schools2020-01-07_10_39_00.sql SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET CREATE SCHEMA ALTER SCHEMA CREATE SCHEMA ALTER SCHEMA CREATE SCHEMA ALTER SCHEMA CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION SET SET CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE COPY 2 COPY 0 COPY 12 COPY 4 COPY 12 COPY 7 COPY 0 COPY 21 COPY 21 COPY 21 COPY 0 COPY 3 COPY 4 COPY 5 COPY 27 COPY 6 COPY 1 COPY 3 COPY 0 COPY 0 ALTER TABLE ALTER TABLE ALTER TABLE GRANT postgres@mongodb2:/home/r2schools/backups$
psql < roles.sql
\c r2schools \dt
For video tutorial visit below link:
In this article, we will see download and load PostgreSQL sample database. PostgreSQL sample database is used for practice purpose only.
We can download the PostgreSQL sample database dvdrental from the github
1. Go to link https://github.com/robconery/dvdrental.
2. Create database with the name dvdrental from either Linux shell or psql shell.
From Linux shell
createdb dvdrental
From psql shell:
create database dvdrental;
3. From the Linux shell navigate to Downloads directory and execute below restore command.
pg_restore -d dvdrental dvdrental.tar
4. Then, connect to psql shell and run below command to verify PostgreSQL sample database loaded or not.
select * from pg_database where datname='dvdrental';
or
\l dvdrental