0

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?

ErocM
  • 4,505
  • 24
  • 94
  • 161
  • 2
    try `pg_restore --clean --create --dbname %pgdb% %dmpfile%` – nbk Aug 23 '23 at 16:30
  • Additional hints: [Why is no string output with 'echo %var%' after using 'set var = text' on command line?](https://stackoverflow.com/a/26388460/3074564) This answer explains why it is advisable to use `set "variable=value"` to define an environment variable. [How to stop Windows command interpreter from quitting batch file execution on an incorrect user input?](https://stackoverflow.com/a/49834019/3074564) This answer explains how to prevent an unexpected exit of the batch file processing on user makes a mistake on entering a string on a prompt. – Mofi Aug 23 '23 at 18:18
  • [Symbol equivalent to NEQ, LSS, GTR, etc. in Windows batch files](https://stackoverflow.com/a/47386323/3074564) This answer explains why the __string__ comparison operator `==` should be used for a string comparison and not the `EQU` integer comparison operator. DosTips forum topic: [ECHO. FAILS to give text or blank line - Instead use ECHO/](https://www.dostips.com/forum/viewtopic.php?f=3&t=774) This forum topic describes why `echo/` or `echo(` should be used instead of `echo.` which results in accessing the file system to find a file with name `echo`. – Mofi Aug 23 '23 at 18:20
  • The __issue__ chapters in [this answer](https://stackoverflow.com/a/60686543/3074564) should be read as well. `%dmpfile%` in the command lines should be enclosed in `"`, except the value assigned to the environment variable `dmpfile` is already enclosed in `"` which in this case depends on the user input on prompt. – Mofi Aug 23 '23 at 18:24
  • @Mofi wow ty, I'll review and adjust accordingly. – ErocM Aug 23 '23 at 20:05

1 Answers1

1

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)

jjanes
  • 37,812
  • 5
  • 27
  • 34