3

I've used many times this technique for transferring databases between servers:

pg_dump --no-owner -Fd mydb -j 4 -f tmp/mydb
scp -r tmp/mydb otherserver:tmp/

then in the other server:

dropdb --if-exists mydb && 
createdb -T template0 mydb && 
pg_restore -j 4 -d mydb tmp/mydb && 
rm -rf tmp/mydb

It has worked flawlessly. Using Postgres 9.3.16 and CentOS 7.

But now, in a new server, pg_restore complains:

pg_restore: [archiver] parallel restore is not supported with this archive file format

I could remove the -j 4 parameter, but theorically it should be valid. According to the pg_restore doc:

-j number-of-jobs --jobs=number-of-jobs

Run the most time-consuming parts of pg_restore — those which load data, create indexes, or create constraints — using multiple concurrent jobs. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine.

Each job is one process or one thread, depending on the operating system, and uses a separate connection to the server.

The optimal value for this option depends on the hardware setup of the server, of the client, and of the network. Factors include the number of CPU cores and the disk setup. A good place to start is the number of CPU cores on the server, but values larger than that can also lead to faster restore times in many cases. Of course, values that are too high will lead to decreased performance because of thrashing.

Only the custom and directory archive formats are supported with this option. The input must be a regular file or directory (not, for example, a pipe). This option is ignored when emitting a script rather than connecting directly to a database server. Also, multiple jobs cannot be used together with the option --single-transaction.

I'm using the directory format in pg_dump: -Fd ! Why does it complain?

  • 1
    Maybe you could check the postgreSQL version on the new server ? – Arkhena May 30 '17 at 11:15
  • 1
    Maybe you could add the -fd option in pg_restore... Documentation says it's not necessary but maybe it could help... – Arkhena May 30 '17 at 11:26
  • Thanks @Arkhena, Version 9.3.16. Adding the -Fd option doesn't help. :-( – david.perez May 30 '17 at 13:54
3

I don't know why, but I have as server Postgres 9.3 and psql, pg_restore and pg_dump belong to version 9.2.

/usr/bin/pg_restore is version 9.2, but /usr/pgsql-9.3/bin/pg_dump is version 9.3.

This is the cause of the problem.

postgresql.x86_64                                     9.2.18-1.el7                                  @base      
postgresql-devel.x86_64                               9.2.18-1.el7                                  @base      
postgresql-libs.x86_64                                9.2.18-1.el7                                  @base      
postgresql-odbc.x86_64                                09.03.0100-2.el7                              @base      
postgresql93.x86_64                                   9.3.17-1PGDG.rhel7                            @pgdg93    
postgresql93-contrib.x86_64                           9.3.17-1PGDG.rhel7                            @pgdg93    
postgresql93-libs.x86_64                              9.3.17-1PGDG.rhel7                            @pgdg93    
postgresql93-server.x86_64                            9.3.17-1PGDG.rhel7                            @pgdg93    

The solution is simply:

yum remove postgresql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.