PostgreSQL, backup/restore with custom functions

Question

For first time I was try to use custom functions in PostgreSQL like first and last what goes good and might be seen here.

Since I use PostgreSQL through .NET I have my functions for backup and restore a database what also goes OK for a long time.
Now, when I have custom functions in PostgreSQL I cannot get archived databases back.
Here is copy from CMD window what system say's:

pg_restore: creating FUNCTION first_agg(anyelement, anyelement)
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 208; 1255 42417 FUNCTION first_ agg(anyelement, anyelement) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: function "first_agg " already exists with same argument types
Command was: CREATE FUNCTION first_agg(anyelement, anyelement) RETURNS anyel ement
LANGUAGE sql IMMUTABLE STRICT
AS $_$ SE...
pg_restore: creating FUNCTION last_agg(anyelement, anyelement)
pg_restore: [archiver (db)] Error from TOC entry 212; 1255 42419 FUNCTION last_a gg(anyelement, anyelement) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: function "last_agg"
already exists with same argument types
Command was: CREATE FUNCTION last_agg(anyelement, anyelement) RETURNS anyele ment
LANGUAGE sql IMMUTABLE STRICT
AS $_$ SEL...
pg_restore: creating AGGREGATE first(anyelement)
pg_restore: [archiver (db)] Error from TOC entry 654; 1255 42418 AGGREGATE first (anyelement) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: function "first" al ready exists with same argument types
Command was: CREATE AGGREGATE first(anyelement) (
SFUNC = first_agg,
STYPE = anyelement
);
pg_restore: creating AGGREGATE last(anyelement)
pg_restore: [archiver (db)] Error from TOC entry 655; 1255 42420 AGGREGATE last( anyelement) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: function "last" alr eady exists with same argument types
Command was: CREATE AGGREGATE last(anyelement) (
SFUNC = last_agg,
STYPE = anyelement
);

This is my commands for backup and restoring:

C:\Program Files (x86)\PostgreSQL\9.3\bin\pg_dump.exe" --host localhost 
--port 5432 --username "postgres" --no-password --verbose -F t 
--file "C:\Archives\mydatabase.tar" "mydatabase"   

C:\Program Files (x86)\PostgreSQL\9.3\bin\pg_restore.exe"  -i -h localhost 
-p 5432 -U "postgres" -d "mydatabase" -v "C:\Archives\mydatabase.tar"

For now I haven't any damage but question is how to make backups and restoring when custom functions are involved? Are they transferrable with data too? Or have to be excluded from backup? How to improve showed backup and restore commands that those processes goes smooth and proper like was before?

It seems you are restoring your backup into a database that already contains those functions (or a different version of them). You either need to drop everything before running pg_restore or use the --clean option to drop everything before importing. Btw: you should be storing the source code of your functions in a version control system (ideally combined with something like Liquibase or Flyway to have a proper deployment process). That is much better than doing a backup of the whole database. — a_horse_with_no_name
@name, Thanks for explanation, I understand now what is a problem. Can you give me some link to proper deployment process? — Wine Too

harmic harmic · Accepted Answer · 2014-01-25T23:17:57

There could be two explanations for this.

The first is that you might be restoring your backup into a database that is not empty and in fact already contains these functions (although there is no guarantee that they are defined the same!) - as per horse's comment. You can use the --clean option of pg_restore to remove such definitions. You could also add the --create option so that it would drop and then recreate the whole database.

Another possibility is that you have these functions defined in the template1 database. Anything defined in that database will be copied to any new database you create. In that case you might be better off dropping these definitions from template1 before restoring.

PostgreSQL, backup/restore with custom functions

1 Answers