Restoring an existing database

Поиск
Список
Период
Сортировка
От Michael Glaesemann
Тема Restoring an existing database
Дата
Msg-id 366D284E-A78F-11D7-9578-0005029FC1A7@myrealbox.com
обсуждение исходный текст
Ответы Re: Restoring an existing database  (Josh Berkus <josh@agliodbs.com>)
Список pgsql-novice
I'd like to back up my database. I've read through the pg_dump and
pg_restore sections in the Reference, and Chapter 9 of the
Administrator's Guide of the online docs that come with 7.3.2. I still
have a few questions that I still have. I guess I'd just like
reassurance that I'm not going to screw up and lose my data!:)

Question 1 (Encoding). My database is UTF-8 encoded. I'm assuming the
archive made by pg_dump will include this encoding and that pg_restore
will have no problem reading it back in. Is this assumption valid? I've
got a lot of Japanese in my database and would hate to lose it.

Question 2 (OIDs). The database includes lots of foreign keys, though
they all reference my own ID numbers, not OIDs. Chapter 9.1 (SQL Dump)
says

<quote>
Important:  When your database schema relies on OIDs (for instance as
foreign keys) you must instruct pg_dump to dump the OIDs as well. To do
this, use the -o command line option. "Large objects" are not dumped by
default, either. See pg_dump's command reference page if you use large
objects.
</quote>

Since my foreign keys don't rely on the OIDs, I don't need to include
OIDs with the dump, correct? (Am I reading that correctly?)

Question 3 (Restoring an existing database). To make everything clean
and pretty, suppose I should use the -c option with pg_restore to drop
the tables before they're recreated. What happens if I don't drop the
tables? I'm guessing it will pretty much trash the database. The reason
I ask is that it would be nice to know that it's just replacing the
data (deleting a row, inserting a row), rather than having to drop the
tables wholesale.

But then again, I guess it has to delete the old, insert the new
anyway. And it's probably a lot faster dropping a whole table than
going through line by line.

Question 4 (Privileges). I undestand that the user who backs up the
data should have read privileges to everything that is being dumped,
and that ownership of the objects being restored is maintained. I'm
wondering about users who have restricted (i.e., not superuser or
ownership) privileges to the database objects. Will these be restored
as well, or will I have to regrant these privileges? It seems to me
that if the object is dropped and recreated, they're not really the
same object--they just share the same name, so the privileges would
need to be re-established.

This might be another reason dumping with OIDs would be important. If
the privileges are based on OID (as I suspect, though I haven't done
any research on this), then the privileges would automatically be
restored.

Question 5 (Transactions). Is the restore done inside one big
transaction block? If it was, then if it failed, the original database
wouldn't be affected.

Thanks a lot for any clarification on these points, or suggestions of
where I should look for further information. It will reassure me that I
won't lose what I've worked on so far. It's my first major database,
and I'm probably being a bit overcautious, but then again, the data is
important, isn't it.

Thanks!

Michael Glaesemann
grzm myrealbox com


В списке pgsql-novice по дате отправления:

Предыдущее
От: Bruno Wolff III
Дата:
Сообщение: Re: Which version?
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: Restoring an existing database