Thread: backup postgres database
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
> 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
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
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
:>
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
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")"
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
-----------------------------------------------------------------
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
-----------------------------------------------------------------
>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.
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
-----------------------------------------------------------------
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")"