Pg dump linux install

How to Backup and Restore PostgreSQL Databases with pg_dump

Pg_dump is a PostgreSQL backup utility tool that allows database administrators to export databases in all forms such as full, copy, incremental, and differential backups. Together with pg_dumpall , the tool can export one or many databases in a single command to either a SQL script file in formats such as .sql , .dump , or an archive file such as .tar , .tgz .

This article explains how you can backup PostgreSQL databases using pg_dump , or pg_dumpall , then restore the databases using the pg_restore tool on a local or remote server such as a Vultr managed database for PostgreSQL cluster.

Prerequisites

Before you begin, you may need to:

• Create a Vultr Managed database for PostgreSQL.
• Verify that your target PostgreSQL version is equal or higher than your source database.

Example

In this section, you’ll install the PostgreSQL client tool that activates the pg_dump utilities. Then, create an example database to use for the backup and restoration steps described in this article.

Install the PostgreSQL client tool on your computer. Debian/Ubuntu

 $ sudo apt install postgresql-client 

 $ sudo dnf install postgresql-client 

 $ psql --host=example.vultrdb.com --port 5432 --username=example-user --password --dbname=postgres 

 CREATE DATABASE example_db; 

 CREATE TABLE app_users ( user_id serial PRIMARY KEY, username VARCHAR ( 50 ) NOT NULL, password VARCHAR ( 50 ) NOT NULL, email VARCHAR ( 255 ) NOT NULL ); 

Backup the PostgreSQL Database

To perform PostgreSQL database backups, pg_dump uses the following syntax.

 pg_dump --host= --port= --username= --dbname= -f .dump 

 $ pg_dump -Fd -v --host=example.vultrdb.com --port=5432--username=example-user --dbname=example_db -f backup.dump 

 $ pg_dumpall -Fd -v --host=example.vultrdb.com --port=5432 --username=example-user --dbname=example_db -f full-backup.dump 

Restore the PostgreSQL Database

To restore your PostgreSQL database, you can either use psql or pg_restore to import the database using the following syntax.

 pg_restore --host=[host] --port= --username=[user] --dbname=[database-name] database.backup 

 $ psql --host=example.vultrdb.com --port 5432 --username=example-user --password --dbname=postgres 

 CREATE DATABASE example_db; 

If you backed up a single database, you’re required to re-create it (in case it does not exist) before restoring it to the server. For full backups, this is not required as all databases are automatically recreated and restored on the server.

 pg_restore --host=example.vultrdb.com --port=5432 --username=example-user --dbname=example_db backup.dump 

Conclusion

In this article, you have backed up and restored a PostgreSQL database using the pg_dump tool. This process is useful when migrating from one PostgreSQL server to another, for example, when migrating your on-premises server to a Vultr managed database for PostgreSQL cluster. For more information, please visit the following resources:

Want to contribute?

You could earn up to $600 by adding new articles.

Источник

How to Install pg_dump and pg_restore on Ubuntu 20.04 LTS (Focal Fossa)

In this article, we will see how to install pg_dump and pg_restore utility on Ubuntu 20.04 LTS (Focal Fossa). pg_dump is a very famous Postgresql utility to take database backup consistently even if the database is in use. It does not block user access to the database. However it comes with a limitation that it can only take backup of a single database. If you are planning to take backup of entire cluster then you need to use pg_dumpall utility. Similarly, pg_restore is also a Postgresql utility to restore the data from the archive created by the pg_dump.

Both of the utilities are extensively used by database administrators across the globe to backup and restore the data. pg_dump can take full, incremental and continuous backup quite easily. When used in combination with pg_restore, pg_dump provides a flexible archival and transfer mechanism. You cannot just restore the data using pg_restore but you can also rebuild the database using this utility. It is also easy to install both the utilities on any Linux distributions. Here we are going to look into the steps to install pg_dump and pg_restore on Ubuntu 20.04 LTS based systems.

How to Install pg_dump and pg_restore on Ubuntu 20.04 LTS (Focal Fossa)

Step 1: Prerequisites

a) You should have a running Ubuntu 20.04 LTS Server.

b) You should have sudo or root access to run privileged commands.

c) You should have apt or apt-get utility available in your Server.

Step 2: Update Your Server

In the first step, you need to download and install all the available updates and then upgrade packages to the latest version by using sudo apt update && sudo apt upgrade command as shown below.

cyberithub@ubuntu:~$ sudo apt update && sudo apt upgrade Hit:1 https://dl.google.com/linux/chrome/deb stable InRelease Get:2 https://dl.winehq.org/wine-builds/ubuntu focal InRelease [8,041 B] Hit:3 http://ppa.launchpad.net/flatpak/stable/ubuntu focal InRelease Hit:4 https://download.sublimetext.com apt/stable/ InRelease Hit:5 http://ppa.launchpad.net/juju/stable/ubuntu focal InRelease Hit:6 http://ppa.launchpad.net/libreoffice/ppa/ubuntu focal InRelease Hit:7 http://ppa.launchpad.net/mojo-maintainers/ppa/ubuntu focal InRelease Get:8 https://dl.winehq.org/wine-builds/ubuntu focal/main amd64 Packages [407 kB] Get:9 https://dl.winehq.org/wine-builds/ubuntu focal/main i386 Packages [408 kB] Hit:10 http://in.archive.ubuntu.com/ubuntu focal InRelease Get:11 http://in.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB] Get:12 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB] Get:13 http://in.archive.ubuntu.com/ubuntu focal-backports InRelease [108 kB] Get:14 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages [2,544 kB] Get:15 http://security.ubuntu.com/ubuntu focal-security/main i386 Packages [590 kB] Get:16 http://security.ubuntu.com/ubuntu focal-security/main amd64 Packages [2,156 kB] Get:17 http://in.archive.ubuntu.com/ubuntu focal-updates/main i386 Packages [820 kB] Get:18 http://in.archive.ubuntu.com/ubuntu focal-updates/main Translation-en [430 kB] Get:19 http://security.ubuntu.com/ubuntu focal-security/main Translation-en [348 kB] .

Step 3: Install pg_dump and pg_restore

In the next step, you can install pg_dump and pg_restore utility from default Ubuntu repo by using sudo apt install postgresql-client postgresql-client-common libpq-dev command as shown below. This will download and install the packages along with all its dependencies.

cyberithub@ubuntu:~$ sudo apt install postgresql-client postgresql-client-common libpq-dev Reading package lists. Done Building dependency tree Reading state information. Done The following additional packages will be installed: postgresql-client-12 Suggested packages: postgresql-doc-12 postgresql-12 The following NEW packages will be installed: libpq-dev postgresql-client postgresql-client-12 postgresql-client-common 0 upgraded, 4 newly installed, 0 to remove and 2 not upgraded. Need to get 169 kB/1,222 kB of archives. After this operation, 4,644 kB of additional disk space will be used. Do you want to continue? [Y/n] Y Get:1 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 libpq-dev amd64 12.14-0ubuntu0.20.04.1 [137 kB] Get:2 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 postgresql-client-common all 214ubuntu0.1 [28.2 kB] Get:3 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 postgresql-client all 12+214ubuntu0.1 [3,940 B] Fetched 169 kB in 2s (93.2 kB/s) Selecting previously unselected package libpq-dev. (Reading database . 217571 files and directories currently installed.) Preparing to unpack . /libpq-dev_12.14-0ubuntu0.20.04.1_amd64.deb . Unpacking libpq-dev (12.14-0ubuntu0.20.04.1) . Selecting previously unselected package postgresql-client-common. Preparing to unpack . /postgresql-client-common_214ubuntu0.1_all.deb . Unpacking postgresql-client-common (214ubuntu0.1) . Selecting previously unselected package postgresql-client-12. Preparing to unpack . /postgresql-client-12_12.14-0ubuntu0.20.04.1_amd64.deb . Unpacking postgresql-client-12 (12.14-0ubuntu0.20.04.1) . Selecting previously unselected package postgresql-client. Preparing to unpack . /postgresql-client_12+214ubuntu0.1_all.deb . Unpacking postgresql-client (12+214ubuntu0.1) . Setting up postgresql-client-common (214ubuntu0.1) . Setting up libpq-dev (12.14-0ubuntu0.20.04.1) . Setting up postgresql-client-12 (12.14-0ubuntu0.20.04.1) . update-alternatives: using /usr/share/postgresql/12/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode Setting up postgresql-client (12+214ubuntu0.1) . Processing triggers for man-db (2.9.1-1) .

Step 4: Check Version

To check the installed version of pg_dump utility, you need to run pg_dump —version command as shown below.

cyberithub@ubuntu:~$ pg_dump --version pg_dump (PostgreSQL) 12.14 (Ubuntu 12.14-0ubuntu0.20.04.1)

Similarly, to check the installed version of pg_restore utility, you need to use pg_restore —version command as shown below.

cyberithub@ubuntu:~$ pg_restore --version pg_restore (PostgreSQL) 12.14 (Ubuntu 12.14-0ubuntu0.20.04.1)

Step 5: Using pg_dump and pg_restore

There are multiple ways to backup and restore PostgreSQL database. You can either login to database server and take backup locally or you can connect remotely to the DB Server and take backup in certain directory in various output formats such as tar, dump or in plain text SQL. For example — if you need to take backup of a database called cyberithub_db locally into the database server then you need to first login to the database using postgres account and run below command to take the dump in plain text SQL file.

postgres@ubuntu:~$ pg_dump cyberithub_db > cyberithub_db.sql

You can also take the dump in tar format by specifying t option with -F switch as shown below.

postgres@ubuntu:~$ pg_dump -F t cyberithub_db > cyberithub_db.tar

Similarly, if you are looking to take the backup in dump format then you need to specify c with -F switch as shown below.

postgres@ubuntu:~$ pg_dump -F c cyberithub_db > cyberithub_db.dump

Likewise, if you are planning to take backup by remotely connecting database server of IP 200.128.16.10 on default Port 5432 then you need to run pg_dump -U postgres -h 200.128.16.10 -p 5432 cyberithub_db > cyberithub_db.sql command as shown below.

cyberithub@ubuntu:~$ pg_dump -U postgres -h 200.128.16.10 -p 5432 cyberithub_db > cyberithub_db.sql

You can also take backup in some directory say example directory in our case by using below command.

postgres@ubuntu:~$ pg_dump -F d cyberithub_db -f example

If you are looking to restore the database from the dump you have taken using pg_dump then you need to use pg_restore -d cyberithub_db cyberithub_db.dump command as shown below.

postgres@ubuntu:~$ pg_restore -d cyberithub_db cyberithub_db.dump

Similarly, you can restore from tar dump using pg_restore -d cyberithub_db cyberithub_db.tar command as shown below.

postgres@ubuntu:~$ pg_restore -d cyberithub_db cyberithub_db.tar

If you saved your backup in some directory called example then you can restore the database from the directory by using pg_restore -d cyberithub_db example command as shown below.

postgres@ubuntu:~$ pg_restore -d cyberithub_db example

Step 6: Uninstall pg_dump and pg_restore

Once you are done using pg_dump and pg_restore command, you can choose to uninstall it from your System by using sudo apt remove postgresql-client postgresql-client-common libpq-dev command as shown below.

cyberithub@ubuntu:~$ sudo apt remove postgresql-client postgresql-client-common libpq-dev Reading package lists. Done Building dependency tree Reading state information. Done The following packages will be REMOVED: libpq-dev postgresql-client postgresql-client-12 postgresql-client-common 0 upgraded, 0 newly installed, 4 to remove and 2 not upgraded. After this operation, 4,644 kB disk space will be freed. Do you want to continue? [Y/n] Y (Reading database . 217888 files and directories currently installed.) Removing libpq-dev (12.14-0ubuntu0.20.04.1) . Removing postgresql-client (12+214ubuntu0.1) . Removing postgresql-client-12 (12.14-0ubuntu0.20.04.1) . Removing postgresql-client-common (214ubuntu0.1) . Processing triggers for man-db (2.9.1-1) .

Источник