pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCan I backup/restore a database in a sql script?
4+ messages / 4 participants
[nested] [flat]
* Can I backup/restore a database in a sql script?
@ 2007-06-22 07:59 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-06-22 08:13 ` Re: Can I backup/restore a database in a sql script? Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-06-22 09:09 ` Re: [GENERAL] Can I backup/restore a database in a sql script? Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 4+ messages in thread
From: Joost Kraaijeveld @ 2007-06-22 07:59 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: pgsql-admin
Hi,
I want to write a sql script in which I backup a database and restore a new (altered) version of that database. Is that possible? If so , can anyone give me an example of how to do that?
I can run it from any command prompt (psql -U postgres template1 < my_db.backup) but I would like it to run from psql (which should give me 1 script for all platforms I must do this: Windows, FreeBSD and Debian)
TIA
Joost
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can I backup/restore a database in a sql script?
2007-06-22 07:59 Can I backup/restore a database in a sql script? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2007-06-22 08:13 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 4+ messages in thread
From: Achilleas Mantzios @ 2007-06-22 08:13 UTC (permalink / raw)
To: pgsql-admin
Στις Παρασκευή 22 Ιούνιος 2007 10:59, ο/η Joost Kraaijeveld έγραψε:
> Hi,
>
> I want to write a sql script in which I backup a database and restore a new
> (altered) version of that database. Is that possible? If so , can anyone
> give me an example of how to do that?
>
> I can run it from any command prompt (psql -U postgres template1 <
> my_db.backup) but I would like it to run from psql (which should give me
> 1 script for all platforms I must do this: Windows, FreeBSD and Debian)
>
With "\!" you can execute shell commands.
So you could probably
\c template1
\! pg_dump your_db > your_db.sql
\! some_processing your_db.sql > your_db_altered.sql
DROP DATABSE your_db
CREATE DATABSE your_db
\i your_db_altered.sql
I dont know if the \! escape works for windows, and in any case pg_dump,
some_processing programs must be in the PATH for Unix/Windows
Debian/FreeBSD behaviour is exactly the same, it is on windows that you should
try to make it work.
> TIA
>
> Joost
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL] Can I backup/restore a database in a sql script?
2007-06-22 07:59 Can I backup/restore a database in a sql script? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2007-06-22 09:09 ` Richard Huxton <dev@archonet.com>
2007-06-22 17:24 ` Re: [GENERAL] Can I backup/restore a database in a sql script? Erik Jones <erik@myemma.com>
1 sibling, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2007-06-22 09:09 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-general@postgresql.org; pgsql-admin
Joost Kraaijeveld wrote:
> Hi,
>
> I want to write a sql script in which I backup a database and restore
> a new (altered) version of that database. Is that possible? If so ,
> can anyone give me an example of how to do that?
>
> I can run it from any command prompt (psql -U postgres template1 <
> my_db.backup) but I would like it to run from psql (which should
> give me 1 script for all platforms I must do this: Windows, FreeBSD
> and Debian)
You can restore using just psql (because pg_dump can produce an sql file
to be processed). I don't know of any way to backup a database that
doesn't use pg_dump.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL] Can I backup/restore a database in a sql script?
2007-06-22 07:59 Can I backup/restore a database in a sql script? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-06-22 09:09 ` Re: [GENERAL] Can I backup/restore a database in a sql script? Richard Huxton <dev@archonet.com>
@ 2007-06-22 17:24 ` Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 4+ messages in thread
From: Erik Jones @ 2007-06-22 17:24 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: Postgres general mailing list <pgsql-general@postgresql.org>; pgsql-admin
On Jun 22, 2007, at 4:09 AM, Richard Huxton wrote:
> Joost Kraaijeveld wrote:
>> Hi,
>> I want to write a sql script in which I backup a database and restore
>> a new (altered) version of that database. Is that possible? If so ,
>> can anyone give me an example of how to do that?
>> I can run it from any command prompt (psql -U postgres template1 <
>> my_db.backup) but I would like it to run from psql (which should
>> give me 1 script for all platforms I must do this: Windows, FreeBSD
>> and Debian)
>
> You can restore using just psql (because pg_dump can produce an sql
> file to be processed). I don't know of any way to backup a database
> that doesn't use pg_dump.
>
You could script a PITR backup and recovery procedure but I doubt any
given single solution would be portable between Windows, Debian, and
FreeBSD given the differences in filesystems and filesystem tools
although I suppose with a lot of careful work it may be possible.
See the chapter on Backup and Restore in the manual for the details
of how PITR works (http://www.postgresql.org/docs/8.2/interactive/
backup.html).
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-06-22 17:24 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-22 07:59 Can I backup/restore a database in a sql script? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-06-22 08:13 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-06-22 09:09 ` Richard Huxton <dev@archonet.com>
2007-06-22 17:24 ` Erik Jones <erik@myemma.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox