patternsqlMinor
Parallel pg_restore unsupported
Viewed 0 times
unsupportedparallelpg_restore
Problem
I've used many times this technique for transferring databases between servers:
then in the other server:
It has worked flawlessly.
Using Postgres 9.3.16 and CentOS 7.
But now, in a new server, pg_restore complains:
I could remove the
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
Why does it complain?
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/mydbIt 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 formatI 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?
Solution
I don't know why, but I have as server Postgres 9.3 and
/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.
The solution is simply:
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 @pgdg93The solution is simply:
yum remove postgresqlCode Snippets
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 @pgdg93yum remove postgresqlContext
StackExchange Database Administrators Q#174892, answer score: 3
Revisions (0)
No revisions yet.