Re: Database migration

I have a 200GB archive I created like this:

% pg_dump -f db1.pga -Fc -b db1

To check that it is good, I tried to restore to another database:

% pg_restore -d db2 db1.pga
[...quickly restores sequences, then...]
pg_restore: [archiver (db)] could not execute query: ERROR: function plpgsql_call_handler already exists with same argument types

Ok, I had installed PLPGSQL in template1, which has been useful in other situations. I drop db2, recreate it, and drop the PLPGSQL language and call handler, then try again:

% pg_restore -d db2 db1.pga
[...restores seqs, tables, keys, then spends several quiet hours (on blobs I assume)...]
pg_restore: [archiver (db)] could not execute query: ERROR: Database comments may only be applied to the current database

I'm not sure how to get around this one. Can an archived database with comments be restored to a database with a different name? Are the comments the only thing missing from the restored database, or could there be other things?

thanks,
Dylan Kuhn

"Kuhn, Dylan K (4520500D)" <Dylan(dot)Kuhn(at)navy(dot)mil> writes:
> [ tries to restore a dump into a database with a different name ]
> pg_restore: [archiver (db)] could not execute query: ERROR: Database commen=
> ts may only be applied to the current database

> I'm not sure how to get around this one. Can an archived database with com=
> ments be restored to a database with a different name?

Hm. Evidently not :-(. The COMMENT ON DATABASE facility is a bit bogus
anyway (since there's no way to make the comments visible across
databases). You might be best advised not to use it.

Hackers: this seems like an extremely bad side-effect of what we thought
was a simple addition of a helpful check. I am thinking we should
either remove the check again, or downgrade it to a WARNING (though I'm
not quite sure how to phrase the warning ...). Any thoughts?

regards, tom lane

> Hm. Evidently not :-(. The COMMENT ON DATABASE facility is a bit bogus
> anyway (since there's no way to make the comments visible across
> databases). You might be best advised not to use it.
>
> Hackers: this seems like an extremely bad side-effect of what we thought
> was a simple addition of a helpful check. I am thinking we should
> either remove the check again, or downgrade it to a WARNING (though I'm
> not quite sure how to phrase the warning ...). Any thoughts?

How about going the other way and removing the requirement to explicitly
state the database?

COMMENT ON DATABASE IS 'This comment is on the current database.';

Rod Taylor <rbt(at)rbt(dot)ca> writes:
>> Hackers: this seems like an extremely bad side-effect of what we thought
>> was a simple addition of a helpful check. I am thinking we should
>> either remove the check again, or downgrade it to a WARNING (though I'm
>> not quite sure how to phrase the warning ...). Any thoughts?

> How about going the other way and removing the requirement to explicitly
> state the database?
> COMMENT ON DATABASE IS 'This comment is on the current database.';

Won't help us for reading existing pg_dump scripts, although perhaps it
would be useful going forward.

Given the current implementation, it seems like there are three possible
behaviors for COMMENT ON DATABASE when the database name isn't the same
as the current database:

1. Raise error (what we're doing now). Simple but breaks dump scripts
for the restore-into-different-DB scenario.

2. Do nothing, store the comment in the current DB's pg_description
(what we did in 7.2). Now that I think about it, this also fails
for different-database restore, since very possibly the attempt
to look up the DB name will fail --- you'll get a no-such-database
error instead of the present error, but it's still unhelpful.

3. Ignore the specified DB name, store the comment as the description
of the current DB; possibly give a warning saying we're doing so.
This would allow correct restoration of dumps into different DBs,
but I think people would find it awfully surprising :-(

regards, tom lane

> 3. Ignore the specified DB name, store the comment as the description
> of the current DB; possibly give a warning saying we're doing so.
> This would allow correct restoration of dumps into different DBs,
> but I think people would find it awfully surprising :-(

I like this one for 7.4 (with warning) but remove the requirement to
supply a dbname at all with a warning about the deprecated syntax in
7.4. 7.4 pg_dump should not provide dbname.

Remove the ability to supply database name completely in 7.5.

On Tue, Jul 15, 2003 at 04:03:13PM -0400, Tom Lane wrote:

> Given the current implementation, it seems like there are three possible
> behaviors for COMMENT ON DATABASE when the database name isn't the same
> as the current database:

There's a fourth possibility: ignore the command and issue a WARNING.
Restores the database in both cases (in same database and in a different
one), and sets the correct comment only if the database name is correct,
giving a hint that the comment should be manually set.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu. Five minutes later I realize that it's also talking
about food" (Donald Knuth)

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> On Tue, Jul 15, 2003 at 04:03:13PM -0400, Tom Lane wrote:
>> Given the current implementation, it seems like there are three possible
>> behaviors for COMMENT ON DATABASE when the database name isn't the same
>> as the current database:

> There's a fourth possibility: ignore the command and issue a WARNING.

Hmm, that seems like a reasonable choice. Anyone have an objection?

regards, tom lane

Tom Lane wrote:

>Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
>
>
>>On Tue, Jul 15, 2003 at 04:03:13PM -0400, Tom Lane wrote:
>>
>>
>>>Given the current implementation, it seems like there are three possible
>>>behaviors for COMMENT ON DATABASE when the database name isn't the same
>>>as the current database:
>>>
>>>
>
>
>
>>There's a fourth possibility: ignore the command and issue a WARNING.
>>
>>
>
>Hmm, that seems like a reasonable choice. Anyone have an objection?
>

My personal experience would lead me to believe that this is the best
option. Count it a vote in favor.

> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Andrew Biagioni

I am trying to migrate a database from Oracle to Postgresql. I am using the
PgAdmin database migration wizard (1.5.60-Dev) to do this. Using ODBC I am
able to migrate small tables but I have a few tables that have over 10
million rows that are failing. I know the reason as I am getting an
"Snapshot too old" error from Oracle but am unable to come up with a
workaround. I prefer using the wizard as it takes care of nulls etc. which I
might have to handle if I went the dump and load route. Can someone give me
an idea on getting around this (other than preventing updates to Oracle).

Also, is there a way to turn off WAL while loading bulk data.

thanks
dilan

"Dilan Arumainathan" <dilan_a(at)impark(dot)com> writes:
> [ Oracle problem ]

You'd probably have better luck asking about that in an Oracle-specific
list.

> Also, is there a way to turn off WAL while loading bulk data.

No. You could consider turning off fsync though.

regards, tom lane

Dilan Arumainathan wrote:

>I am trying to migrate a database from Oracle to Postgresql. I am using the
>PgAdmin database migration wizard (1.5.60-Dev) to do this. Using ODBC I am
>able to migrate small tables but I have a few tables that have over 10
>million rows that are failing. I know the reason as I am getting an
>"Snapshot too old" error from Oracle but am unable to come up with a
>workaround. I prefer using the wizard as it takes care of nulls etc. which I
>might have to handle if I went the dump and load route. Can someone give me
>an idea on getting around this (other than preventing updates to Oracle).
>
>Also, is there a way to turn off WAL while loading bulk data.
>
>thanks
>dilan
>
Hi Dilan,
this is an well known problem in Oracle.
(I know its off topic, but it might interest other people)
You got several options around it:
- Increase the size of your rollback segments (the message means, that
Oralce cannot give you a consistent view)
See

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c23cnsis.htm#2599
- Stop all other transactions
- Do the work in serveral steps
- Use a script like "unload.sql" ( in the Attachement, from
http://www.evergreen-database.com/)
( it doesn not prevent the problem, but is quite performant)

I hope I could help you with that.
Cheers, Dani