Thread: How to Backup like in mysql or ms sql server

> I'm new bie in postgresql.
> I use postgresql 8.1 windows version.
> How to back-up database in postgresql? Usually i use pgAdmin III by back-up
> and restore.
> Is there any way to back-up database like mysql or sql server we just copy
> and paste. Or maybe there is any tools to copy database when the service is
> shutdown.
>
> Where is postgresql put teh database files?

I am not sure about mysql or sql Server, but the following link will how how it is done on the
command line of the PostgreSQL server:
http://www.postgresql.org/docs/8.1/interactive/backup.html

Is is a good place to start.  Also, if you are interested in keeping your data backed up current
within the last few minutes, check out point in time recovery (PITR).  But for simple backup and
restore, I expect that you will be satisfied with the simple pg_dump command.

Regards,

Richard Broersma Jr.

On windows the database files are under C:\program files\postgresql\8.1
\data

There are a number of different ways a backup can be done and a file
level copy/paste is one of them.

See http://www.postgresql.org/docs/8.1/interactive/backup.html

Mike

On Sun, 2006-07-02 at 11:01 +0700, Joko Siswanto wrote:
>
>
> Hi all,
>
> I'm new bie in postgresql.
> I use postgresql 8.1 windows version.
> How to back-up database in postgresql? Usually i use pgAdmin III by
> back-up and restore.
> Is there any way to back-up database like mysql or sql server we just
> copy and paste. Or maybe there is any tools to copy database when the
> service is shutdown.
>
> Where is postgresql put teh database files?
>
> Thanks all,
> Jokonet
>
>

You can technically just copy & paste the postgresql data directory IF YOU
SHUT DOWN THE POSTMASTER FIRST! Be aware that this will only work for the
same version of postgresql. Also: this is not a good way to do it and I'd
encourage you not to use this as general means of backup (it's ok if you want
to create a quick clone of an existing database on a second machine -
provided that the platform and postgresql version on there is identical to
the source).

The proper way would be to use pg_dump (a tool that comes along every
postgresql installation - see the docs for usage), which will dump the data
and structure.
There is a second such utility program called pg_restore which will take the
dump-file created with pg_dump and restore it to a database of your choosing.
Those dumps will be functional between versions and platforms - so that's the
way to go.

UC


On Saturday 01 July 2006 21:01, Joko Siswanto wrote:
> Hi all,
>
> I'm new bie in postgresql.
> I use postgresql 8.1 windows version.
> How to back-up database in postgresql? Usually i use pgAdmin III by back-up
> and restore.
> Is there any way to back-up database like mysql or sql server we just copy
> and paste. Or maybe there is any tools to copy database when the service is
> shutdown.
>
> Where is postgresql put teh database files?
>
> Thanks all,
> Jokonet

--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416

Uwe C. Schroeder wrote:
> You can technically just copy & paste the postgresql data directory IF YOU
> SHUT DOWN THE POSTMASTER FIRST! Be aware that this will only work for the
> same version of postgresql. Also: this is not a good way to do it and I'd
> encourage you not to use this as general means of backup (it's ok if you want
> to create a quick clone of an existing database on a second machine -
> provided that the platform and postgresql version on there is identical to
> the source).


Well, err. thats not completely true with current postgres versions:

http://www.postgresql.org/docs/current/static/backup-online.html

...
>> Is there any way to back-up database like mysql or sql server we just copy
>> and paste. Or maybe there is any tools to copy database when the service is
>> shutdown.
>>
>> Where is postgresql put teh database files?

Well, thats in the docs ;) (or see above)

btw, just "copy and paste" w/o preparation is
dangerous with the above databases too.

Regards
Tino

On Sunday 02 July 2006 01:42, Tino Wildenhain wrote:
> Uwe C. Schroeder wrote:
> > You can technically just copy & paste the postgresql data directory IF
> > YOU SHUT DOWN THE POSTMASTER FIRST! Be aware that this will only work for
> > the same version of postgresql. Also: this is not a good way to do it and
> > I'd encourage you not to use this as general means of backup (it's ok if
> > you want to create a quick clone of an existing database on a second
> > machine - provided that the platform and postgresql version on there is
> > identical to the source).
>
> Well, err. thats not completely true with current postgres versions:
>
> http://www.postgresql.org/docs/current/static/backup-online.html
>
> ...


Ok, you're correct on that one. However I'd rather not encourage someone to
mess with WAL and filesystem based backups when s/he hasn't even heard of
pg_dump yet, simply because I can already see the next question popping
up ... like in "I had a failure and wanted to restore my backup, but
everything is messed up now and I can't get it running - help please!" :-)
With a standard pg_dump that won't happen, so it's IMHO the safest way to deal
with the backup problem for a newbie.

On a side-note: that piece of documentation is pretty heavy reading and
assumes quite some knowledge about how a DB system like postgresql works
internally. For me it's always the least sophisticated approach that solves a
given problem. The good old KISS principle applies again :-)

Uwe



>
> >> Is there any way to back-up database like mysql or sql server we just
> >> copy and paste. Or maybe there is any tools to copy database when the
> >> service is shutdown.
> >>
> >> Where is postgresql put teh database files?
>
> Well, thats in the docs ;) (or see above)
>
> btw, just "copy and paste" w/o preparation is
> dangerous with the above databases too.
>
> Regards
> Tino

--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416