Обсуждение: search_path when restoring to new db
It seems to me that if I dump and restore a database as a new db I need to manually issue ALTER DATABASE dbname SET search_path=schema1, schema2; to get back the search_path of the original db. I s this realy the case or I am doing something wrong and there is a way to have the backup-restore do this automatically? Thanks. Balázs
"SunWuKung" <Balazs.Klein@t-online.hu> writes:
> It seems to me that if I dump and restore a database as a new db I need
> to manually issue
> ALTER DATABASE dbname SET search_path=schema1, schema2;
> to get back the search_path of the original db.
ALTER DATABASE and ALTER USER commands are handled by pg_dumpall, not
pg_dump.
regards, tom lane
On Mon, 2006-12-11 at 10:06, Tom Lane wrote: > "SunWuKung" <Balazs.Klein@t-online.hu> writes: > > It seems to me that if I dump and restore a database as a new db I need > > to manually issue > > ALTER DATABASE dbname SET search_path=schema1, schema2; > > to get back the search_path of the original db. > > ALTER DATABASE and ALTER USER commands are handled by pg_dumpall, not > pg_dump. And you can use pg_dumpall -s to get those: pg_dumpall -s|grep -i "alter database"|grep -i search_path
Thanks for your answers. I guess it means that I have to issue Alter database after restore than. I find this a little scary because this is something that I just realised now and wouldn't have thought beforehand. Seems to me that to make an exact copy of a single db it isn't enough to use pg_backup and pg_restore but need to issue other commands as well. I think it would help a few users (it would certainly help me) if I could find a complete example on how to backup a single database on one server and restore it on another without having to worry about things that might have been omitted - like this one. I used to work with SQLServer and while I like Postgres a lot I don't know enough to take advantage of the flexibility that this backup-restore mechanism offers. In SQLServer if I had the proper rights I could take a backup of a db, bring it to another server, say restore and that's it (ok, I had to repopulate fulltext index) but I could be sure that the db is in the same state than when I made the backup. I must say that this was very convenient for me. I think one should be able to create a backup and restore script that would do the same with Postgres, but I didn't find one so far. Could somebody show me an example? Or trying to simplify this is a wrong aim? Thanks for your help. Bal�zs
PostgreSQL is simply very granular about what it lets you dump. The major point to realize is that pg_dump is useful
forgetting your data schema and data, and pg_dumpall will capture data schema, data, and most database instance
configurations. Pg_dumpall has access to *global* objects, such as roles, tablespaces, and the default search_path.
Pg_dump assumes you're planning on using it to restore to an existing database that's already correctly configured.
Pg_dumpalldoesn't assume that. However, it does assume that the new database server will be the same version of
PostgreSQL,IIRC. I believe it complains if you try to pg_dumpall a database with a different version of pg_dumpall.
--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of SunWuKung
Sent: Monday, December 11, 2006 3:56 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] search_path when restoring to new db
Thanks for your answers.
I guess it means that I have to issue Alter database after restore
than.
I find this a little scary because this is something that I just
realised now and wouldn't have thought beforehand. Seems to me that to
make an exact copy of a single db it isn't enough to use pg_backup and
pg_restore but need to issue other commands as well.
I think it would help a few users (it would certainly help me) if I
could find a complete example on how to backup a single database on one
server and restore it on another without having to worry about things
that might have been omitted - like this one.
I used to work with SQLServer and while I like Postgres a lot I don't
know enough to take advantage of the flexibility that this
backup-restore mechanism offers. In SQLServer if I had the proper
rights I could take a backup of a db, bring it to another server, say
restore and that's it (ok, I had to repopulate fulltext index) but I
could be sure that the db is in the same state than when I made the
backup. I must say that this was very convenient for me.
I think one should be able to create a backup and restore script that
would do the same with Postgres, but I didn't find one so far. Could
somebody show me an example? Or trying to simplify this is a wrong aim?
Thanks for your help.
Balázs
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Ok, I understand this difference now. Knowing that, what is the standard way to copy a single database to another server? - can I use pg_dumpall to dump a single db? - or - I have to use pg_dump and there is a procedure to ensure that old and new dbs are the same, like 1. create new db 2. check or create user 3. run pg_restore 4. do alter db and is there a way to automate this? If there is could you point me there? If there isn't: Do you think that automating the complete backup and restore of a single database would be database specific or it could work with different databases? Don't you think that this is something that would be generally useful? Thank you for the help. Balázs
The standard method is to use a pg_dumpall for the initial copy and whenever globals or the schema changes, and use pg_dumpwhen you just want to get the data from a single database. Globals and schema should not change very often. In fact, they should be fixed except between software revisions. If theyaren't, you might be doing something wrong. What you can do is pg_dumpall --schema-only or pg_dumpall --globals-only (I'm not sure if one of those is a subset of theother) and then use pg_dump --data-only for the databases you want data for. I'm not sure offhand which options you'llneed to use to be sure of getting what you want. Double check the docs and be sure to test it a few times. -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of SunWuKung Sent: Tuesday, December 12, 2006 3:19 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] search_path when restoring to new db Ok, I understand this difference now. Knowing that, what is the standard way to copy a single database to another server? - can I use pg_dumpall to dump a single db? - or - I have to use pg_dump and there is a procedure to ensure that old and new dbs are the same, like 1. create new db 2. check or create user 3. run pg_restore 4. do alter db and is there a way to automate this? If there is could you point me there? If there isn't: Do you think that automating the complete backup and restore of a single database would be database specific or it could work with different databases? Don't you think that this is something that would be generally useful? Thank you for the help. Balázs ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
Brandon Aiken wrote: > PostgreSQL is simply very granular about what it lets you dump. True enough, but I'd think you could make a good argument that dumping a database should dump any ALTER commands that are attached to it. Users are shared between databases, so I can see it doesn't necessarily make sense to dump them. However, an ALTER DATABASE is meaningless without the database concerned. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> True enough, but I'd think you could make a good argument that dumping a
> database should dump any ALTER commands that are attached to it.
Let's suppose pg_dump did that, so "pg_dump foo >foo.dump" includes
commands like
ALTER DATABASE foo SET ...
Now what happens when you try to load the script in database bar?
Nothing very good ...
regards, tom lane
Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: >> True enough, but I'd think you could make a good argument that dumping a >> database should dump any ALTER commands that are attached to it. > > Let's suppose pg_dump did that, so "pg_dump foo >foo.dump" includes > commands like > > ALTER DATABASE foo SET ... > > Now what happens when you try to load the script in database bar? > Nothing very good ... Which would point to the need to allow "ALTER DATABASE current_database()" or similar I'd say. -- Richard Huxton Archonet Ltd
I think there should be an easier way to backup a single database and restore it on another server. In my case we are developing a db so there are many schema changes to that. When there is a significant change we find it easier to drop and recreate the db from the backup - withouth affecting the other databases that are running on the server. For that now I am using a batch file that creates the db from template0, creates language pgsql, runs pg_restore, alters the db to set searchpath The way I found out that I have to create language before and set the searchpath after running restore is that I found that the restored db doesn't work - which was quite scary at first. The fact that you have to create roles before restore is well documented, but I didn't find the others, so maybe there are still steps missing I just didn't find the error in the restored db yet - which is still scary. If you are saying that I should read the backup-restore in the manual more carefully I can't argue with that, but I have to say for myselft that I have read it several times by now. On one hand it seems that 'PostgreSQL is simply very granular about what it lets you dump' on the other hand it seems that the only unit that you can restore on a clean server without scripting is the whole cluster - which if I understand it correctly is not something you can define, but is simply given as all the databases on the server. I apreciate that my case is special and once things settled people aren't messing so much with their schemas, but how do providers do this? Users of a provider surely can only create the backup of their own db? How would the user restore that backup on his own server? Thanks for the help. Bal�zs
"SunWuKung" <Balazs.Klein@t-online.hu> writes:
> The way I found out that I have to create language before and set the
> searchpath after running restore is that I found that the restored db
> doesn't work - which was quite scary at first.
You should not need to create the language --- that *is* part of the
pg_dump data.
regards, tom lane