Thread: backup postgres database

backup postgres database

From
arun kv
Date:
hello sir,
        i have a postgresql database in one system and want to transfer
the same to another system. i am not finding a way to transfer this
database from one system to another.if i transfer the file in
/var/lib/pgsql/libdatabase them it gives error.how shall i transfer
database.
  another thing. how to take up backups of the database on the system.
pls help me as i am new to postgresql
  thanking you
    Arun


Re: backup postgres database

From
"Corrado Paparo"
Date:
> hello sir,
>         i have a postgresql database in one system and want to transfer
> the same to another system. i am not finding a way to transfer this
> database from one system to another.if i transfer the file in
> /var/lib/pgsql/libdatabase them it gives error.how shall i transfer
> database.
>   another thing. how to take up backups of the database on the system.
> pls help me as i am new to postgresql
>   thanking you
>     Arun

You can use the pg_dump utility (to extract a PostgreSQL database into a
script file or other archive file) and the pg_restore utility (restore a
Postgres database from an archive file created by pg_dump) or the psql
interactive client.
(see the 'Examples' on PostgreSQL 7.1 Documentation, PostgreSQL Client
Applications reference)

Bye
Corrado



Re: backup postgres database

From
"Josh Berkus"
Date:
Arun,

>         i have a postgresql database in one system and want to
>  transfer
> the same to another system. i am not finding a way to transfer this
> database from one system to another.if i transfer the file in
> /var/lib/pgsql/libdatabase them it gives error.how shall i transfer
> database.
>   another thing. how to take up backups of the database on the
>  system.
> pls help me as i am new to postgresql
>   thanking you

Both are accomplished the same way.  Please read "pg_dump" and
 "pg_dumpall" in the online documentation, under "command-line
  utilities".

-Josh

Re: backup postgres database

From
Marcelo Pereira
Date:
Hello,

You could use:

# pg_dump database > backup_file

So, on the another system:

# psql -d database -f backup_file

I think it will solve your problem...

See ya,

Marcelo Pereira

-- Remember that only God and Esc+:w saves.
        __
       (_.\           Marcelo Pereira       |
        / / ___                             |
       / (_/ _ \__    Matematica/99 - IMECC |
_______\____/_\___)___Unicamp_______________/

--- arun kv, with his fast fingers, wrote:

:> hello sir,
:>         i have a postgresql database in one system and want to transfer
:> the same to another system. i am not finding a way to transfer this
:> database from one system to another.if i transfer the file in
:> /var/lib/pgsql/libdatabase them it gives error.how shall i transfer
:> database.
:>   another thing. how to take up backups of the database on the system.
:> pls help me as i am new to postgresql
:>   thanking you
:>     Arun
:>
:>
:> ---------------------------(end of broadcast)---------------------------
:> TIP 4: Don't 'kill -9' the postmaster
:>


Re: backup postgres database

From
Jon Hassen
Date:
try using the command:

/usr/local/pgsql/bin/pg_dumpall > outputfile

('outputfile' is the name you want for your file) to make a backup of your
database. put that copy where your new site will be. then use the new site
to restore this file as if it belonged there in the first place by using
the command:

/usr/local/pgsql/bin/psql -d template1 -f outputfile

('outputfile' is the name you used in step 1) this restores the file you
created in the first step to the new installation.

that's it. you might have to run initdb, or initlocation on the new
installation's data directory.

Jon Hassen


At 7:42 PM +0530 3/13/02, arun kv wrote:
>hello sir,
>        i have a postgresql database in one system and want to transfer
>the same to another system. i am not finding a way to transfer this
>database from one system to another.if i transfer the file in
>/var/lib/pgsql/libdatabase them it gives error.how shall i transfer
>database.
>  another thing. how to take up backups of the database on the system.
>pls help me as i am new to postgresql
>  thanking you
>    Arun
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster




Re: backup postgres database

From
Frank Finner
Date:
Hi,

pg_dump and psql are your friends.

Dump the database with pg_dump, take the file and insert it into psql
on the other machine. Details are covered in the manpages of these
programs.

Crude (without options): pg_dump your_db > your_db.dump
                         psql < your_db.dump

In the manpages you also find the options to dump with all create
statements (e.g. CREATE DATABASE on the new machine) you might need.

pg_dump is also THE solution to make backups. If you simply take the
database files (especially, if the engine is running), you probably
cannot use them either for backup or for transferring due to caching
and other things. With pg_dump you even can transfer your database to
another database engine (from PostgreSQL to Oracle for example), if you
dump it with the option of complete insert statements.

mfg frank finner


On 13-Mar-02 arun kv sat down, thought for a long time and then wrote:
> hello sir,
>         i have a postgresql database in one system and want to
> transfer
> the same to another system. i am not finding a way to transfer this
> database from one system to another.if i transfer the file in
> /var/lib/pgsql/libdatabase them it gives error.how shall i transfer
> database.
>   another thing. how to take up backups of the database on the
> system.
> pls help me as i am new to postgresql
>   thanking you
>     Arun
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Frank Finner

And now there is no turning back at all.
                              (M. Moorcock, "Elric Of Melnibone")"

Re: backup postgres database

From
Martín Marqués
Date:
On Mié 13 Mar 2002 14:31, you wrote:
> > hello sir,
> >         i have a postgresql database in one system and want to transfer
> > the same to another system. i am not finding a way to transfer this
> > database from one system to another.if i transfer the file in
> > /var/lib/pgsql/libdatabase them it gives error.how shall i transfer
> > database.
> >   another thing. how to take up backups of the database on the system.
> > pls help me as i am new to postgresql
> >   thanking you
> >     Arun
>
> You can use the pg_dump utility (to extract a PostgreSQL database into a
> script file or other archive file) and the pg_restore utility (restore a
> Postgres database from an archive file created by pg_dump) or the psql
> interactive client.
> (see the 'Examples' on PostgreSQL 7.1 Documentation, PostgreSQL Client
> Applications reference)

Isn't it that

psql template1 > dump-file

also works? That's how I restore a backup.

--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Re: backup postgres database

From
Martín Marqués
Date:
On Mié 13 Mar 2002 15:51, you wrote:
> Hi,
>
> pg_dump and psql are your friends.
>
> Dump the database with pg_dump, take the file and insert it into psql
> on the other machine. Details are covered in the manpages of these
> programs.
>
> Crude (without options): pg_dump your_db > your_db.dump
>                          psql < your_db.dump

This will almost always give an error (even though lots of docs have this
sintaxis). That's because you didn't tell psql which database to connect to,
so he'll try to connect to a database with the name of the user (in my case,
that would be martin, or postgres in the case of administration duties).

My 2 cents

--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Re: backup postgres database

From
Chris
Date:
>On Mié 13 Mar 2002 14:31, you wrote:
> > > hello sir,
> > >         i have a postgresql database in one system and want to transfer
> > > the same to another system. i am not finding a way to transfer this
> > > database from one system to another.if i transfer the file in
> > > /var/lib/pgsql/libdatabase them it gives error.how shall i transfer
> > > database.
> > >   another thing. how to take up backups of the database on the system.
> > > pls help me as i am new to postgresql
> > >   thanking you
> > >     Arun
> >
> > You can use the pg_dump utility (to extract a PostgreSQL database into a
> > script file or other archive file) and the pg_restore utility (restore a
> > Postgres database from an archive file created by pg_dump) or the psql
> > interactive client.
> > (see the 'Examples' on PostgreSQL 7.1 Documentation, PostgreSQL Client
> > Applications reference)
>
>Isn't it that
>
>psql template1 > dump-file
>
>also works? That's how I restore a backup.

That would be dumping template1 not restoring to it :) (Change the > to <
and you've got it right).

Chris.


Re: backup postgres database

From
Martín Marqués
Date:
On Mié 13 Mar 2002 22:38, you wrote:
> >
> >Isn't it that
> >
> >psql template1 > dump-file
> >
> >also works? That's how I restore a backup.
>
> That would be dumping template1 not restoring to it :) (Change the > to <
> and you've got it right).

:-)

Right! Lucky the mistake was on a mail and not on the command line. :-)

Saludos... :-)

--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Re: backup postgres database

From
Frank Finner
Date:
Hi Martin!

Ah, stupid me, you are right. Even if one has ALL create statements
within the dump, including the "create database", one must connect to a
database to be able to run the commands within the dump.

If in doubt, which database to use, one should always be able to
connect to database "template1" without great possibilities to damage
something (except one connects as user postgres) and run the dumped
commands, because "template1" is always there in a postgres
installation. But please be sure not to insert your tables into
template1, because everything what is in this database is used as
template for every new database! Just use it as a "connection database"
and run some commands similiar to

Create Database "my_db";
\connect my_db my_username

as the first commands of the dump (this is what "pg_dump -C <my_db>"
gives you)!

Another reason to carefully study the manpages... ;-)

Greetings, Frank.



On 13-Mar-02 Martín Marqués sat down, thought for a long time and then
wrote:
> On Mié 13 Mar 2002 15:51, you wrote:
>> Hi,
>>
>> pg_dump and psql are your friends.
>>
>> Dump the database with pg_dump, take the file and insert it into
>> psql
>> on the other machine. Details are covered in the manpages of these
>> programs.
>>
>> Crude (without options): pg_dump your_db > your_db.dump
>>                          psql < your_db.dump
>
> This will almost always give an error (even though lots of docs have
> this
> sintaxis). That's because you didn't tell psql which database to
> connect to,
> so he'll try to connect to a database with the name of the user (in
> my case,
> that would be martin, or postgres in the case of administration
> duties).
>
> My 2 cents
>
> --
> Porqué usar una base de datos relacional cualquiera,
> si podés usar PostgreSQL?
> -----------------------------------------------------------------
> Martín Marqués                  |        mmarques@unl.edu.ar
> Programador, Administrador, DBA |       Centro de Telematica
>                        Universidad Nacional
>                             del Litoral
> -----------------------------------------------------------------
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org

--
Frank Finner

And now there is no turning back at all.
                              (M. Moorcock, "Elric Of Melnibone")"