MY PREFERRED METHOD (Databases or Tables- here from Windows client)

Overview (click to go straight to STEP)

• STEP 1 : Preparation
• STEP 2 : Make the Dump File*
• STEP 3 : Restore to Remote Server*
• STEP 4 : Quick Reconciliation Check

Introduction

This is very much getting towards professional database administration but it seems like a good methodology that I found understandable. My reading of online information is that you can use these tools to dump and restore extremely large databases. I’ve read people using this for everything up to 5TB databases online so I expect this will cover every requirement I will ever need.

My testing indicates you can use pg_dump command to backup any PostgreSQL database. My reading indicates that this can be done even if others are accessing the database, pg_dump will still back it up, and it will not block others from reading or writing to it. (unless you max out the CPU)

Because backup and restore is so important I try to practice it regularly and I am always looking to refine my methodology, issuing the dump and restore commands now takes me seconds combined, while the actual dump and restore times vary directly with the size of database and specs of the server , for the 2GB database I tested, it was taking about 5 minutes at dump and another 5 minutes at restore. The first time you attempt this I recommend you set aside a morning as you might have to install some command line tools (check with your organisation if this is allowed) and read through everything carefully. At February 2025 from my admittedly limited experience I have found using the Custom Format flag (-F c) with the PG_Dump command line tool (the backups should have a suffix *.dump) to be the most reliable and flexible way of making a single file that can be accurately transferred and restored not just within the same postgres server instance but between postgres server instances. PG_Dump can backup to a number of formats (p(plain text), c (custom archive), d(directory) and t(tar archive)). I’ve only investigated plain and custom format. I have chosen to use custom format because it compresses the resulting file (my 2gb database dumped to a 818 MB file), it is quicker to complete, can handle large databases, handles extensions better (here postgis) and you can restore individual tables, schemas or functions from a single dump file. I have personally tested it on a database with over 4 million records with a size of about 2 GB. Many of those records were spatially enabled and the whole database had postgis installed (The example data I used was all planning applications from 14 years for the whole of Scotland along with 69 other tables available from the Spatial Hub – conservation areas / T.P.Os etc.). I was able to consistently 100% accurately transfer the database between servers that were both local and remote. My research indicates custom format (sometimes referred to as dump format) is postgres specific and unique to pg_dump. As it stands at February 2025 this has been the best way I have found to backup & restore individual Postgres databases and tables. Of course your IT section will likely be backing up the entire server. Day to day I will use pgAdmin to look at databases but I found both limits to the file sizes that I could work with and reliability particularly on restore.

As ever performing database backups is absolutely vital on so many levels. Knowing where your data is, how to back it up and restore it with regular backups of the system of record is your best defence against data breach, data corruption, server failure or server room/building failure. As such I’m never totally happy until I know how to backup the databases I am working on , I have practiced that backup routine and I know how to restore them between servers. A database isn’t particularly useful unless it is available online remotely from a server thus all of the important databases I work with are remote. Additionally transfering an existing database somewhere to work on is particularly useful if you don’t want to pollute your database when experimenting with code and I sometime backup a database to work on a particularly complicated stored procedure or edit and then transfer the code to production after I have optimised and tested on Test. Likewise a backup isn’t particularly safe if it is on the same server that the database is on or if it has never been restored before (the backups might be corrupt) You won’t connect to a database unless you have a username a password and all other connection parameters so it is an important rule when administering database backup and restore that you know your connection parameters keep them secure. If you are administering a database realise it is not simply out there somewhere it is on a specific machine with a specific address and someone needs to know that.

Very often you may have central support from professional database administration nonetheless as a domain expert often you have a better focus on the particularly important information and its an extra pair of eyes to monitor threats.

STEP 1 – Preparation

Using Command Line Tools

We will be using command line tools extensively here, not everyone likes them particularly because they are just not intuitive but I found them the most reliable way of getting large databases and tables between servers / databases. I have personally tested these Command Tools from a windows machine to a server that is on Linux distro.

Requirements for success

You will need to have all of the following to be successful (both for dumping and restoring databases or tables)

• Postgres Command Line Tools installed on the machine you are working (Version No. > Postgres Server Version No.)
• Postgres Server hostnames
• Postgres Port numbers (usually the default 5432)
• Usernames
• Username’s password
• Name of Database you wish to backup
• Source table name if you wish to backup only tables (if you only want to save a table or tables)
• A target local directory where the dumped file will go (here called dump your mileage may vary)

Additional recommendations

• Try to match version numbers of the postgres servers you are transferring databases between.
• Practice on a database containing real world data that you understand which is fairly large if you can.

A note on Postgres Command Line Tools (Must be equal or higher version No. than Postgres version No.)

The default installation of postgres installs something called Command Line Tools (which includes psql / pg_restore and pg_dump executables). The install comes packaged with Postgres and you may already have a version of these tools if postgres has been installed locally. If not see the Command Line Installer link below to get the installation executables. IMPORTANT you must have an equal or higher version of the Command Line Tools than the Postgres Server Version you wish to backup / restore, failure to have this could mean pg_dump or pg_restore will refuse to start. Separate version installations of Command Line tools can exist on the same machine, as can postgres server versions. the command line tools can also be installed by themselves without a postgres server installation. You can choose which executable versions (Command line tool version number mirror and are released with postgres versions eg. v14 / v16 etc) you use by altering the directory in the DOS prompt to match your intent. (Explained later)

Get Postgres and Command Line Installer here

Screenshot of postgres download page

At command line install you can restrict installation to only the Command Line Tools.

STEP 2 – Make the Dump file

Now first navigate to where the command line utilities are in this case I am wanting to export from a postgres version 16 server and I have the 16 tooling installed so I navigate to the 16\bin directory. Default installation of postgres utilites is within a version number that relates to the version number subdirectory

c:\Program Files\PostgreSQL\16\bin

Go to the DOS prompt, (on a windows machine type cmd in the windows search bar) : below is the quick way of navigating to the directories where the pg_dump and pg_executables are.

cd /d "C:\Program Files\PostgreSQL\16\bin"

Initiate a dump of an entire database here named dbase03

PG_DUMP – My preferred dump command template (Feb 2025)

pg_dump --no-owner --no-acl -h prod_server_01 -p 5432 -U rolemark -d dbase03 -F c -f "c:/dump/20250201dbase03_backup.dump"

Quick Command Creator

From ChatGPT

• --no-owner: Prevents dumping commands that set object ownership.
• --no-acl: (Optional) Prevents dumping access privileges, so you avoid issues if roles differ between systems.
• -h: host or server name here prod_server_01
• -p: port number the default of most postgres installations is 5432
• -U: username here rolemark
• -d: Name of the database here dbase03
• -F: Format of the output dump here c instructs the ‘dump’ format unique to pg_dump the main alternative is p which will produce a sql format file.
• -f: filename of the dump and here I list not just its filename but where I want to put it AND its suffix – note if you don’t specify the suffix it won’t have one. So literally if you call it dbase03.xls it will build it as this filename but it will still be dump format!

Alternative command that I sometimes use

pg_dump -h prod_server_01 -p 5432 -U rolemark -d dbase03 -F c -f "c:/dump/20250201dbase03_backup.dump"

Then hit enter you will be prompted for your password.

WORD of Caution : Entering password at command line is awkward as the cursor will not move position when typing for this reason I usually copy and paste my password in (again even pasting the cursor location doesn’t change) (copy and paste in command line may not be possible depending on your computers default settings): Stick with it – might look like nothing is being entered but it is.

There will be a delay while the database is dumped. Be aware that this delay can last minutes and there is no feedback to indicate anything is happening, in fact it very much looks like the command window has frozen. Be patient this is normal. I tend to leave the computer completely alone at this point.. (Good time for a coffee) Once complete you will be able to see the dump file at the location targeted and the command line prompt reappears blank.

To initiate the dump of an individual table we can use a command similar to below.

pg_dump -h prod_server_01 -p 5432 -U rolemark -d dbase03 -t t001tablename -F c -f "c:/dump/t001tablename_backup.dump"

–

STEP 3 – Restore to Remote Server (or local further down)

Pg_restore will not by default create the named target database in your target postgres server if it does not already exist in fact you MUST create the exact database with any extensions used in your pg_restore command BEFORE you trigger the pg_restore. You can name the new database anything you want and it does not need to be the same name as your dump file, obviously the name you use must be repeated in the pg_restore command.

Does pg_restore overwrite? From my reading the pg_restore can use a clean option (-c) which will overwrite an existing database. None of the listed commands here include this flag (the c variable in pg_dump is a parameter of -F). Using this if the table already exists an error will appear if the clean option is not used, but pg_restore will proceed unless the exit-on-error option is used. I do not know if -c is a complete or partial overwrite. I have not tested that flag so I recommend thorough testing if you need to overwrite a database. The reconcilliation check will be good in that respect.

CREATE DATABASE dbase03;
CREATE EXTENSION postgis;

Now ensure you are in correct directory

cd /d "C:\Program Files\PostgreSQL\16\bin"

And trigger the install

PG_RESTORE – My preferred restore command template (Feb 2025)

pg_restore --no-owner -h prod_server_01 -p 5432 -U rolemark -d dbase03 "c:/dump/20250101dbase03_backup.dump"

Quick Command Creator

• --no-owner: Prevents restoring commands that set object ownership. Ownership will be set to the person doing the restore here rolemark
• -h: host or server name here prod_server_01
• -p: port number default at installation is 5432
• -U: Username here rolemark
• -d: Name of the database into which the dump will be placed here dbase03 must be created beforehand and all extensions added before the dump file is restored.

On enter you will again be asked for your password and again you should be patient as if successful nothing will appear to be working for minutes. Again time to leave the terminal alone and be patient.

And here is what it looks like immediately after having successfully restored the spatialhub practice dataset to my local postgres server. Several things – I’m using the postgres superuser here because its my server and also its on port 5435 because I have postgres version 12 and 14 also installed on this machine and they have taken the 5432 and 5434 ports respectively. After hitting return on password this took about 3 minutes to restore a 2 GB file. Time to restore will be affected by the power of the server you are restoring to.

Alternative Command I sometimes use…

pg_restore -h prod_server_01 -p 5432 -U rolemark -d db004target "c:/dump/20250101db004_backup.dump"

I am told that pg_restore does not recognize the –no-acl flag, which we used in pg_dump. Instead, if you need to skip restoring access privileges (the GRANT/REVOKE commands), you can use the -x (or –no-privileges) option with pg_restore. For example:

pg_restore -x -d target_database dumpfile

This option tells pg_restore to ignore any access privilege commands in the dump. If your dump was already created with pg_dump’s –no-acl option, then there shouldn’t be any ACL commands in the file, and you wouldn’t need to specify -x during restore.

You will then be asked for your password and then after that if successful it will just come up with the directory again. (note you can add a -c flag to this which will clean (overwrite) the database if it already exists prior to import. When I tried that it came back with a series of errors as it tried to drop tables in the target database that did not exist this was off putting so I removed the -c flag. I figure usally when I am doing a restore I will simply create a new database and I do not need to be warned that it is trying to drop a table that does not exist – my reading indicates that I will still get errors if other issues arise even with the -c flag.

Restore to Local

Remember your parameters may vary localhost is a default as is 5432 your username and password will be unique and you will need to be in the correct bin sub directory of postgres and have created the target database with extensions prior to import.

pg_restore --no-owner -h localhost -p 5432 -U rolemark -d db004target "c:/dump/20250101db004_backup.dump"

Restore individual table from a dump file

See below.. You do not need to create the table before you restore a table from a dump file BUT the database need to exist. You can install an individual table or tables from a full database dump – one of the advantages of pg_restore from dump format. This is not possible with some of the other pg_dump formats. The below command restores two tables from a full database dump you can increase or decrease the number of tables restored.

pg_restore -h prod_server_01 -p 5432 -U rolemark -d db004target -t table1 -t table2 "c:/dump/20250101db004_backup.dump"

–

STEP 4 – Quick Reconciliation Check

Pg_restore gives no feedback on the success of the restore but will produce errors if it hasn’t succeeded. It should be noted that default behaviour seems to be that it reports failures but continues with the restore and failures relating to ownership such as roles sometimes do not affect the import of data. Either way this is disconcerting and so I like to do a quick reconciliation between source database and target database to give added confidence that everything has restored correctly. I run the following pSQL against the database prior to dumping and then again on the restored database post restore to compare total number of records, total number of tables and total number of views. This should catch obvious failures I recommend that anything less than a perfect 100% reconciliation should be investigated.

DO $$
DECLARE
    rec         RECORD;
    rec_count   bigint;
    total_rows  bigint := 0;
    total_items int := 0;
BEGIN
    -- Create a temporary table to store individual counts
    CREATE TEMP TABLE temp_table_counts (
        table_name text,
        row_count  bigint
    ) ON COMMIT DROP;
 
    -- Loop through each user table and view (exclude system schemas)
    FOR rec IN
        SELECT table_schema, table_name, table_type
        FROM information_schema.tables
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
          AND table_type IN ('BASE TABLE', 'VIEW')
        ORDER BY table_name
    LOOP
        BEGIN
            -- Dynamically count the rows for the current table/view
            EXECUTE format('SELECT count(*) FROM %I.%I', rec.table_schema, rec.table_name)
            INTO rec_count;
        EXCEPTION WHEN OTHERS THEN
            -- In case of error (for example, if the view is not countable), set to NULL
            rec_count := NULL;
        END;
 
        -- Insert the result into our temporary table.
        INSERT INTO temp_table_counts(table_name, row_count)
        VALUES (rec.table_schema || '.' || rec.table_name, rec_count);
 
        -- Accumulate totals (if count succeeded)
        IF rec_count IS NOT NULL THEN
            total_rows := total_rows + rec_count;
        END IF;
        total_items := total_items + 1;
    END LOOP;
 
    -- Return the per-table results
    RAISE NOTICE 'Table Name | Row Count';
    FOR rec IN
        SELECT table_name, row_count 
        FROM temp_table_counts 
        ORDER BY table_name
    LOOP
        RAISE NOTICE '% | %', rec.table_name, rec.row_count;
    END LOOP;
 
    -- Return the summary totals
    RAISE NOTICE '--------------------------------------';
    RAISE NOTICE 'Total Rows: %, Total Tables/Views: %', total_rows, total_items;
END $$;

Here I am running this in PGAdmin against the source and restored databases and check for discrepancies. It will list out all of the tables and then at the foot show Total Rows / Total Tables/Views.. see below screenshot (my SpatialHub data)