Backup and Restore PostgreSQL database (.sql) using C# .NET

Links

GitHub Repository — https://github.com/joemoceri/database-toolkit

Intro

We’ll be using .NET 6 and PostgreSQL 14.1. See the GitHub repository below for the latest with comments and more.

Backup

In order to backup a PostgreSQL database we can use pg_dump, a command line tool for backing up PostgreSQL databases, and the following C# method and appsettings.json.

There are three properties in the appsettings.json file we want to set.

"PostgreSQLHost": "localhost",

"PostgreSQLPort": "5432",

"PostgreSQLUser": "postgres",

These properties must match exactly what’s in your pgpass.conf file. Postgres on Windows looks for pgpass.conf in the following location, you must create the directory and file if it doesn’t exist already.

%AppData%\postgresql\pgpass.conf

Inside your pgpass.conf file make sure it looks like the following, replacing localhost (server), 5432 (port), database, username, and password with your own. See here for more information on the pgpass.conf file.

The final command that’s used within the postgres-backup.bat file will look like this. -h specifies the host, -p specifies the port, -U specifies the username and we place the name of the database at the end that we’re backing up. backup.sql is your localDatabasePath where the backup will be saved.

pg_dump -h localhost -p 5432 -U username -Fc database > backup.sql

The -Fc flag tells pg_dump to output with a custom-format that’s usable with pg_restore, which we’ll use when restoring a PostgreSQL database. For a full list of the switches available for pg_dump see the documentation here.

Restore

To restore a PostgreSQL database we can use the following C# method and the same appsettings.json from above.

We need to use pg_restore, a command line utility for Postgres that can restore a database created by pg_dump.

Make sure the same settings in your appsettings.json are set from above.

"PostgreSQLHost": "localhost",

"PostgreSQLPort": "5432",

"PostgreSQLUser": "postgres",

We’re using the same mechanism with pgpass.conf above, so make sure what you have in your pgpass.conf file (host, port, database, username, password) matches what’s here.

The final command will look like this, where backup.sql is the path to the database backup file we’re restoring. -h, -p, and -U are the same as above, -c drops the database first before restoring, and -d specifies the database. We end it with the localDatabasePath which is backup.sql, where the database backup file we’re restoring is stored.

pg_restore -h localhost -p 5432 -U user -c -d database backup.sql