Skip to main content

Backup/Restore using PostgreSQL database in linux

This document contains steps to perform backup and restore operations of OPNBI 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

    info

    Running pg_lsclusters will list all the Postgres clusters running on your device

    if the Status shown is down in your case, restart PostgreSQL service.

    Docusaurus Slash Introduction

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

    info

    Use this command to restart service sudo pg_ctlcluster 14 main start & then execute command pg_lsclusters to check status of Postgres clusters.

    Here 14 is the version displayed in Postgres clusters list. provide version number which you want to start service.

    You can skip this restart command if Status is online

    Docusaurus Slash Introduction

    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

    Docusaurus Slash Introduction

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

    Docusaurus Slash Introduction

  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.

    Docusaurus Slash Introduction

    Your database is succesfully restored if no errors are displayed.

For Docker Users​

  1. Pull docker image of postgres database docker pull postgres
info

If you already have docker image of postgresql, skip this step

  1. 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

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

  2. Install updates once you connect with container root@2328ea11890d:/# apt update

  3. Install Postgres database root@2328ea11890d:/# apt install postgresql postgresql-contrib

  4. Check postgresql installed version root@2328ea11890d:/# apt show postgresql

  5. Switch over to the postgres account on your server by typing: root@2328ea11890d:/# su - postgres

  6. You can now access the PostgreSQL prompt immediately by typing:postgres@2328ea11890d:~$ psql

  7. 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
);

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

  2. 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');

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

  2. Exit from postgresql by typing \q and enter

  3. 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:

    postgres@2328ea11890d:~$ pg_dump postgres > postgres_backup.bak

  4. Create new DB for restoring backup file postgres@BhaumikLaptop:~$ createdb backup

Note

If you already have database to restore the backup file, skip this step

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

    postgres@BhaumikLaptop:~$ psql backup < postgres_backup.bak

  2. login to backup user to check datarestored or not: postgres@BhaumikLaptop:~$ psql -d backup

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