Restore from backup failed

andrewp · November 16, 2010, 3:36pm

Chris_Smith · November 16, 2010, 9:22pm

Hi,

[quote]

I hope someone can help me with this one, as although this is just our Training setup, we have a training session in 2 days' time!

I took a backup of our training installation (SLES 11, PGSQL). I restored it from backup today, following instructions from:http://matrix.squiz.net/resources/upgrading/backup-management and it's failed.

I'm getting:

Undefined function: 7 ERROR: function sq_get_lineage_from_url(unknown) does not exist LINE 7: SELECT * FROM sq_get_lineage_from_url($1) ^ HINT: No function matches the given name and argument types.

When I look in the DB I notice that all the PG_CATALOG functions are there, but none of the PUBLIC functions such as:

public | asset_link_treeid_convert | bytea | bytea, character, integer, integer

public | atoi | integer | character varying

public | sq_get_lineage_from_url | setof character varying | character varying

public | sq_get_lineage_treeids | setof bytea | character varying, integer

public | sq_get_lineage_treeids | setof bytea | character varying, integer, timestamp without time zone

public | sq_get_parent_treeids | setof bytea | character varying, integer

public | sq_get_parent_treeids | setof bytea | character varying, integer, timestamp without time zone

public | sq_get_rollback_timestamp | timestamp without time zone |

public | sq_grant_access | text | character varying

public | sq_rb_get_lineage_treeids | setof bytea | character varying, integer

public | sq_rb_get_parent_treeids | setof bytea | character varying, integer

public | sq_set_rollback_timestamp | void |

public | sq_set_rollback_timestamp | void | timestamp without time zone

Anyone any ideas on how I can get it working?

thanks

Andrew

[/quote]

Were there any errors during the backup? Did you use the matrix script to do the backup?

What's in the db dump? Just data? What about table structures?

If you still have the old system around you can do a 'pg_dump -s' which will only grab the functions and tables (and views etc) but no data.

eg:

pg_dump -s -U $matrix_db_user $matrix_db > tables.dump

then restore that:

psql -U $matrix_db_user $matrix_db < tables.dump

and ignore the errors about tables that already exist.

andrewp · November 17, 2010, 8:42am

Thanks for that Chris.
I ran the 'pg_dump -s' command and all is fine now.

I'm a little perplexed as to why this happened. I simply ran the backup using:

./scripts/backup.sh /srv/mysource_matrix /squiz-backups/

There were no errors during backup, nor during restore.

The restored DB (from what I could see) had all the Squiz tables with data, but just none of the functions.

Could it be because I'm running the backup as root?

Andrew

[quote]

Hi,

Were there any errors during the backup? Did you use the matrix script to do the backup?

What's in the db dump? Just data? What about table structures?

If you still have the old system around you can do a 'pg_dump -s' which will only grab the functions and tables (and views etc) but no data.

eg:

pg_dump -s -U $matrix_db_user $matrix_db > tables.dump

then restore that:

psql -U $matrix_db_user $matrix_db < tables.dump

and ignore the errors about tables that already exist.

[/quote]

Chris_Smith · November 17, 2010, 9:50pm

Hi Andrew,

[quote]

Thanks for that Chris.

I ran the 'pg_dump -s' command and all is fine now.

I'm a little perplexed as to why this happened. I simply ran the backup using:

./scripts/backup.sh /srv/mysource_matrix /squiz-backups/

There were no errors during backup, nor during restore.

The restored DB (from what I could see) had all the Squiz tables with data, but just none of the functions.

Could it be because I'm running the backup as root?

Andrew

[/quote]

No, that should be fine. The backup script handles passwords etc from the matrix data/private/conf/db.inc file. I'm a little confused too. Was it just one backup that was broken or are all of the database dumps like that (no functions) ?

What version of matrix and postgres?