Forum OpenACS Q&A: Howto move Postgres database

Collapse
Posted by Malte Sussdorff on
How do I move a postgres database (lets call it "mydb") from server
"foo" to server "bar".

I tried "pg_dump -f mydb.dmp -F c -b mydb" on foo and "pg_restore -F c
-d mydb mydb.dmp" on bar (after scping it over) but I get strange errors :

pg_restore: creating FUNCTION "tree_ancestor_keys" (bit varying,integer)

pg_restore: [archiver (db)] could not execute query: ERROR:  Function
'tree_ancestor_keys(varbit, int4)' does not exist
        Unable to identify a function that satisfies the given
argument types
        You may need to add explicit typecasts

Any idea, maybe a quick howto how you do it normally ?

Collapse
Posted by Tilmann Singer on
Maybe you need to run
psql -f packages/acs-kernel/sql/postgresql/postgresql.sql
before restoring.
Collapse
Posted by Vadim Makarov on
You need to apply two patches during restore (scroll down to readers's comments).

I think you can also find this info by searching the forums for "tree_ancestor_keys" or something like this.

Collapse
Posted by Peter Alberer on
Hi Malte,

when you look at the creation code of the tree_ancestor things you will notice that some they are created by dynamically written queries. (I think this is because they both call the other or something funny like that) So you need to create these two functions manually after restoring the dump. In my experience, this was the only problem moving a db like that, i did it a few times.

ciao,