Thread: Problem in offline backup & restore

> I am trying to implement backup and restore feature.
> For backup I am issuing the command from windows cmd prompt:
> C:\ [path to pg_dump.exe ]\pg_dump.exe -i -h localhost -p 5432 -U postgres
> -F c -b -v -f C:\MyBackups\db_backup.backup mydb
> For  restore I am issuing the following command:
> C:\[path to pg_restore.exe ]\pg_restore.exe -i -h localhost -p 5432 -U
> postgres -d mydb -c -v C:\MyBackups\db_backup.backup
> The above 2 commands working perfect while database is online (running).
> However the problem is they are not working while database is offline
> (database shutdown)
> It is showing the following error message:
> pg_dump: [archiver (db)] connection to database "mydb" failed: could not
> connect
> to server: Connection refused (0x0000274D/10061)
>         Is the server running on host "localhost" and accepting
>         TCP/IP connections on port 5432?
> pg_dump: *** aborted because of error
> Is it possible to backup & restore the database offline? If so what is the
> problem in my command.

The error message you received is correct due to what you are trying to achieve.  When you
shutdown the postmaster, there is no longer any PostgreSQL service to process any of your
commands.

The only way that I can think of to back-up the database while the postgresql service is down, is
to copy the entire postgresql data directory to another location.  However, I highly doubt this is
what you really want. However if you know what you are doing it is possible but less safe than a
regular pg_dump'ed file.

Regards,

Richard Broersma Jr.

On Tue, 2006-09-05 at 19:33 +0530, Sree Narayana wrote:
> Hi
>
> I am trying to implement backup and restore feature.
>
> For backup I am issuing the command from windows cmd prompt:
>
> C:\ [path to pg_dump.exe ]\pg_dump.exe -i -h localhost -p 5432 -U
> postgres -F c -b -v -f C:\MyBackups\db_backup.backup mydb
>
> For  restore I am issuing the following command:
> C:\[path to pg_restore.exe ]\pg_restore.exe -i -h localhost -p 5432 -U
> postgres -d mydb -c -v C:\MyBackups\db_backup.backup
>
> The above 2 commands working perfect while database is online
> (running).
>
> However the problem is they are not working while database is offline
> (database shutdown)
>
> It is showing the following error message:
>
> pg_dump: [archiver (db)] connection to database "mydb" failed: could
> not connect
> to server: Connection refused (0x0000274D/10061)
>         Is the server running on host "localhost" and accepting
>         TCP/IP connections on port 5432?
> pg_dump: *** aborted because of error
>
> Is it possible to backup & restore the database offline? If so what is
> the problem in my command.
>
> Please help me.
>
> Thanks
>
> Sreeman

Morning Sreeman,

The backup and restore commands are commands TO a running instance of
postgresql.  Without the server running they can't do anything.  If
you're looking for a backup solution that doesn't require a running
instance of postgresql then look at doing a file system backup.  For
this method to work postgresql CAN'T be running so you have to shut it
down before backing up and before restoring.

I do believe most people use the pgdump or pgdumpall method rather than
the file system method.

John Purser