postgresqlbatch-filewindows-server-2022

Creating a PostgreSQL restore batch file for Windows and cannot get the parameters correct

I am in the process of creating a backup script for my server running PostgreSQL on Windows 2022.

I am rather new to Postgres and I've searched this until I'm blue in the face but my switches seem to contradict each other when I'm running the script.

Here's my script so far:

@echo off
cls

set PGHOST=localhost
set PGUSER=someuser
set PGPASSWORD=somepassword
set PGPORT=5432
set PGDB=gitlab

echo This script will restore a PostgreSQL database dump file.
set /p "dmpfile=Enter dump file name to restore:      "
set /p "pgdb=Enter the new database name (gitlab): "
if "%pgdb%" equ "" set "pgdb=gitlab"

echo.
echo Importing %dmpfile% into database %pgdb%
echo.

echo off

echo Creating database %pgdb%
echo.
createdb -h %pghost% -p %pgport% -U %pguser% -T template0 %pgdb%

echo.
echo Importing %dmpfile%
echo.
pg_restore --create --dbname %pgdb% %dmpfile%

If I do not have the createdb line, I'm getting this message:

pg_restore: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: database "gitlab" does not exist

If I do create the database with createdb, then when the restore runs, I'm getting this message:

pg_restore: error: could not execute query: ERROR: database "gitlab" already exists Command was: CREATE DATABASE gitlab WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'English_United States.1252';

I don't get it. If I create it, it says it already exists, if I don't it complains that it doesn't exist.

What am I doing wrong?

Solution

  • When you use --create for pg_restore, the db specified on the command line is the "utility" database which gets connected to in order to execute the CREATE DATABASE command. This would generally be 'postgres' or 'template1'. In this case the name of the database which is to be created and restored into is coming from within the dump file, it is not specified on the command line.

    So either precreate the database, but don't use --create, and do specify the name of that precreated database to restore into. Or don't precreate the database and do use --create, but then specify the name of a different already-existing utility database.

    If you want the name of restored database do differ from the name it was originally dumped with, then you must use the first option (or just go rename it afterwards)