In the little projects I am doing and using a database, sometimes I have the need to either backup and restore a database from and to the same server, but also when I want to do some development while traveling I want to have a copy of the database or databases in my laptop.

I use PostgreSQL for my databases and I rely on the utilities supplied by the PostgreSQL installation namely the pg-dump, psql and pg_restore for doing these copies and backup/restore.

The process I follow it is very simple:
1. backup a database using pg_dump.
2. restore a database using psql or pg_restore.

Sometimes the backup is to have a copy before I make any changes or implement new functionality that involves the database and other times is to copy the backup file onto another computer to be then restored on it.

backup a database using pg_dump

From the manual page for [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html) at the start of the description it says this "pg_dump is a utility for exporting a PostgreSQL database. It makes consistent exports even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers). Note, however, that except in simple cases, pg_dump is generally not the right choice for taking regular backups of production databases."

Just left this here as a note, so to set the expectations.

This is the command line I use when doing most of the "dumps" or backups, starting as root:
# su postgres
$ pg_dump --clean --create database_name > database_name.sql
I su into postgres user, and then I do the pg_dump with options:

--clean - again from the manual page for pg_dump "Output commands to DROP all the dumped database objects prior to outputting the commands for creating them. This option is useful when the restore is to overwrite an existing database. ". This is the what I mostly do, I will overwrite and existing database, be it in the same host or to move to another server.

--create - also from the manual page "Begin the output with a command to create the database itself and reconnect to the created database. (With a script of this form, it doesn't matter which database in the destination installation you connect to before running the script.) If --clean is also specified, the script drops and recreates the target database before reconnecting to it."

And of course the last is the database name. I then redirect the output to a a file, with the extension sql, as we are no providing any other options, this will be the most basic dump which is done in pure SQL text format.

You can then check the contents of the generated file:

$ more database_name.sql

--
-- PostgreSQL database dump
--

\restrict 840Qjf4aEaUnGHnzo55HotcYifcAybpWlxKDooiKxyBNWzlfj9RpIpjVv8pyYdT

-- Dumped from database version 16.11
-- Dumped by pg_dump version 16.11

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE database_name;
--
-- Name: database_name; Type: DATABASE; Schema: -; Owner: database_user
--
...
$

For a simple explanation, as you can see, some initial output that shows the versions of the server and pg_dump that was used, followed by a collection of "SET"s to set some options and the first command as stated from the above options, to DROP the database before recreating it.

restore a database using psql or pg_restore

Having done the dump of the database we wanted, then depending on what we want to do, restore the backup or copy the file to another computer with PostgreSQL to be restored there.

As stated, I am doing the most basic dump, which is in text format containing SQL commands, and because of this the only option to restore is to run the output sql file with or through psql.

In my case I will do it like this, su into postgres user and start psql:
# su postgres
$ psql
psql (16.11)
Type "help" for help.

postgres=#

I am assuming I am in the local directory where I have the output file, database_name.sql, so I can then just "run" it from within psql:

postgres=# \i database_name.sql
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET
DROP DATABASE
CREATE DATABASE
...
...
CREATE INDEX
database_name=# \d
Did not find any relations.
database_name=# \c database_name
database_name=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------------+----------+---------
public | table1 | table | testuser
public | table1_id_seq | sequence | testuser
public | table2 | table | testuser
public | table2_id_seq | sequence | testuser
public | users | table | testuser
public | users_id_seq | sequence | testuser


As you see, the SQL script runs, and the time it takes depending on the size of the file, and when finished, you will be back into the psql command prompt, kind of connected to the database you just restored, but I could see that if I issued the psql command "\d" to show the tables of the database, I would get a message the above "Did not find any relations.".

I then just did a "\c database_name" and issued again the "\d" and I could see a list of the tables.

This is the most simplest way of doing it considering I am talking about small, or even, very small databases that I am using, and it simply works.
Of course when planning on doing a regular backup there are other tools more suited for this, but when i came to handling that i will try to write something about it.