Обсуждение: BUG #3682: Incomplete database restore
The following bug has been logged online: Bug reference: 3682 Logged by: Gary Chambers Email address: gwchamb@gmail.com PostgreSQL version: 8.3beta1 Operating system: Mac OS X 10.4.10 Description: Incomplete database restore Details: After dumping a database with pg_dump, then attempting to restore it, the restore fails with "invalid command \" error reports. Each individual database is dumped by: pg_dump --username=<owner> --file=<filename>
Gary Chambers wrote: > The following bug has been logged online: > > Bug reference: 3682 > Logged by: Gary Chambers > Email address: gwchamb@gmail.com > PostgreSQL version: 8.3beta1 > Operating system: Mac OS X 10.4.10 > Description: Incomplete database restore > Details: > > After dumping a database with pg_dump, then attempting to restore it, the > restore fails with "invalid command \" error reports. > > Each individual database is dumped by: > pg_dump --username=<owner> --file=<filename> We're going to need more details. How did you attempt to restore it? Can you post the dump file? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: > Gary Chambers wrote: >> The following bug has been logged online: >> >> Bug reference: 3682 >> Logged by: Gary Chambers >> Email address: gwchamb@gmail.com >> PostgreSQL version: 8.3beta1 >> Operating system: Mac OS X 10.4.10 >> Description: Incomplete database restore >> Details: >> >> After dumping a database with pg_dump, then attempting to restore it, the >> restore fails with "invalid command \" error reports. >> >> Each individual database is dumped by: >> pg_dump --username=<owner> --file=<filename> > > We're going to need more details. How did you attempt to restore it? Can > you post the dump file? (Gary sent me the dump off-list.) Ah, did you dump the database from an existing 8.2 installation? There's clauses in there to create Postgres 8.2 contrib/tsearch2 types and functions, which are no longer needed beacuse full-text search is now a core feature. I removed them by hand, and after that it works. We desperately need a migration guide for existing contrib/tsearch2 users... Andy and Tom just put together a script to help with that: http://archives.postgresql.org/pgsql-hackers/2007-10/msg00750.php Can you try that to see if it works? I also had to modify the two CREATE TABLE statements that contained a "public.tsvector" column; it's just "tsvector" now since it's now a built-in type. I'm not good at perl, but I believe the script doesn't do that yet. Note that you have to make a dump with pg_dump -Fc or -Ft option, the one you posted doesn't work with the script. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
(please keep the list cc'd) Gary Chambers wrote: >> Ah, did you dump the database from an existing 8.2 installation? There's >> clauses in there to create Postgres 8.2 contrib/tsearch2 types and >> functions, which are no longer needed beacuse full-text search is now a >> core feature. I removed them by hand, and after that it works. > > Indeed, I did. I thought there may be some trouble with that. I used > pg_dumpall (from the 8.3beta1 source tree prior to installing) then > performed (as the postgres user) a 'psql postgres < dumpall.sql' > restore to the newly-installed 8.3b1 without error. I didn't start > fearing my data's integrity until I noticed that the full-text > searches on the Wiki failed. Thank goodness for development laptops > to perform this kind of testing before upgrading production servers. > :) > >> Note that you have to make a dump with pg_dump -Fc or -Ft option, the one you posted >> doesn't work with the script. > > No luck. I am still receiving numerous invalid command errors. I > tried with both -Fc and -Ft dump types. I tried both piping the > output to psql and using psql to input the pg_restore output. > > You can find the newtoc file and another 8.2.4 database dump at: > ... Ok, thanks. I ran the script like this: ./removets2 wikidb.tar > newtoc pg_restore -L newtoc wikidb.tar > wikidb-psql Then I edited wikidb-psql, changing those "public.tsvector" datatypes to just "tsvector". After that: psql wikidb < wikidb-psql And got no errors. What should we do with the data type in the script? Currently it just looks at the TOC, but that's not enough to search/replace schema.tsvector to tsvector. Is it enough if we mention that in the migration guide? Perhaps we should include the functionality of that script in pg_dump. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Perhaps we should include the functionality of that script in pg_dump.
I'm pretty uncomfortable with the notion of having pg_dump deliberately
throw data away. Another problem is that even if we did that, it would
only help people who dumped their old DB with 8.3 pg_dump.
Having the functionality in pg_restore would be a little saner, but it
still seems like a big wart.
As for the datatype issue, I wonder whether we should just advise people
to do
CREATE DOMAIN public.tsvector AS pg_catalog.tsvector;
before restoring?
regards, tom lane
Heikki... > Then I edited wikidb-psql, changing those "public.tsvector" datatypes to > just "tsvector". After that: > psql wikidb < wikidb-psql > And got no errors. Okay, thanks! I hadn't removed the two instances of the schema prepending the tsvector type. I can now import the data into 8.3beta1. The mediawiki software is still broken, but that's outside the scope of this discussion. On more point that I might mention when using the removets2 script (in the case of the Wiki database at least) is that it appears that one must omit the schema parameter or else there will be many more tsearch2-related items with which to deal. Thanks for the help! -- Gary Chambers // Nothing fancy and nothing Microsoft!
On Sun, Oct 21, 2007 at 04:24:05PM -0400, Tom Lane wrote: > "Heikki Linnakangas" <heikki@enterprisedb.com> writes: > > Perhaps we should include the functionality of that script in pg_dump. > > I'm pretty uncomfortable with the notion of having pg_dump deliberately > throw data away. Another problem is that even if we did that, it would > only help people who dumped their old DB with 8.3 pg_dump. Don't we already say you should use 8.3 pg_dump to dump your old db when upgrading? > Having the functionality in pg_restore would be a little saner, but it > still seems like a big wart. > > As for the datatype issue, I wonder whether we should just advise people > to do > CREATE DOMAIN public.tsvector AS pg_catalog.tsvector; > before restoring? Unless it's something that's only temporary, I would prefer something that would migrate the actual schema instead of requiring a domain sitting there *forever*. The overhead isn't large, but it's there... //Magnus
Tom Lane wrote: > As for the datatype issue, I wonder whether we should just advise people > to do > CREATE DOMAIN public.tsvector AS pg_catalog.tsvector; > before restoring? Would be tidier in the long run to replace the references, but that's simpler. Could we do that automatically to the dump file in removets2? I guess not by just tinkering the TOC. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Magnus Hagander <magnus@hagander.net> writes:
> On Sun, Oct 21, 2007 at 04:24:05PM -0400, Tom Lane wrote:
>> I'm pretty uncomfortable with the notion of having pg_dump deliberately
>> throw data away. Another problem is that even if we did that, it would
>> only help people who dumped their old DB with 8.3 pg_dump.
> Don't we already say you should use 8.3 pg_dump to dump your old db when
> upgrading?
We recommend that, but we've never required it, and in a lot of scenarios
it's not all that practical to expect people to do that. In any case it
would be more sensible to put the functionality in pg_restore, if we
were going to do any such thing.
What I'm more worried about is that none of the proposed solutions will
help someone with a pg_dumpall dump, since that will be a flat SQL
script ...
regards, tom lane