Backup Restore PostgreSQL database in linux

This document contains steps to perform backup and restore operations of aiv Database with PostgreSQL database in linux platform.

References

Links usefull for backup and restore data are listed here,

1. https://phoenixnap.com/kb/how-to-install-postgresql-on-ubuntu

2. https://www.digitalocean.com/community/tutorials/how-to-create-remove-manage-tables-in-postgresql-on-a-cloud-server

3. https://www.microfocus.com/documentation/idol/IDOL_12_0/MediaServer/Guides/html/English/Content/Getting_Started/Configure/_TRN_Set_up_PostgreSQL_Linux.htm

4. https://phoenixnap.com/kb/how-to-install-postgresql-on-ubuntu

There are 2 ways we can perform this,

1. Using terminal

2. PgAdmin application

1. Using terminal

Backup

List of commands used to perform backup and restore operation are listed below,

1. List All clusters running on your machine by rinning this command pg_lsclusters

   

   Here ver displayed in Postgres clusters is the version of postgres database installed in machine

   

   Please refer this link for your reference. Click Here

2. Log into the default PostgreSQL user called postgres. If you have different user name enter user name accordingly before executing this command.

   sudo su - postgres

   

   If you are connected to PostgreSQL and want to see details of the connection, use the command: \conninfo

   

3. exit from postgres user to take backup of database. use \q and hit enter command to exit.

4. take a backup file of postgres database by executing below command,

   pg_dump postgres > postgres_backup.bak

   Here pg_dump is used to take dump of database postgres and on the right side of arrow operator is name of backup file.

   Your database is succesfully backup if no errors are displayed.

Restore

To restore postgres backup file (.bak) format follow below steps;

1. Open terminal log into the default PostgreSQL user called postgres. If you have different user name enter user name accordingly before executing this command.

   sudo su - postgres

2. Execute below command to restore postgres database from .bak file;

   psql backup < postgres_backup.bak

   Here backup is name of the database where you want to restore backup database and on the right side of arrow operator is name of backup file.

   

   Your database is succesfully restored if no errors are displayed.

For Docker Users

1. Pull docker image of postgres database docker pull postgres

2. Get the list if docker images for postgres docker ps you will see container ID as gievn in below code,

CONTAINER ID   IMAGE      COMMAND                  CREATED        STATUS          PORTS      NAMES
2328ea11890d   postgres   "docker-entrypoint.s…"   46 hours ago   Up 19 seconds   5432/tcp   some-postgres

3. Connect with postgres containerID PS C:\Users\aivhu> docker exec -it 2328ea11890d bash

4. Install updates once you connect with container apt update

5. Install Postgres database apt install postgresql postgresql-contrib

6. Check postgresql installed version apt show postgresql

7. Switch over to the postgres account on your server by typing: su - postgres

8. You can now access the PostgreSQL prompt immediately by typing:psql

9. Our database does not have any tables yet. We can verify this by asking PostgreSQL to give us a listing of the available tables with this command: \d

11.Now that you know how to connect to the PostgreSQL database system, you can learn some basic Postgres management tasks.

The basic syntax for creating tables is as follows:

    CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

As you can see, these commands give the table a name, and then define the columns as well as the column type and the max length of the field data. You can also optionally add table constraints for each column.

You can learn more about how to create and manage tables in Postgres here.

For demonstration purposes, create the following table:

 CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

12. You can see your new table by typing: \d or \dt to see created table in database postgres

13. Now that you have a table, you can insert some data into it. As an example, add a slide and a swing by calling the table you want to add to, naming the columns and then providing data for each column, like this:

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

14. Retrieve the information you’ve added by typing: SELECT * FROM playground;

15. Exit from postgresql by typing \q and enter

16. PostgreSQL includes a utility called “pg_dump” that can be used to dump database information into a file for backup purposes.

    The pg_dump utility is run from the Linux command line. The basic syntax of the command is:

    pg_dump postgres > postgres_backup.bak

17. Create new DB for restoring backup file createdb backup

18. To restore a backup created by pg_dump, you can redirect the file into psql standard input:

    psql backup < postgres_backup.bak

19. login to backup user to check datarestored or not: psql -d backup

20. Check database created type: \d and it will show tables backed up in this database from backup file