2 edited title
| link

Permission denied to cratecreate "pg_catalog.tablename" during pg_dump targetting another machine

1
source | link

Permission denied to crate "pg_catalog.tablename" during pg_dump targetting another machine

I am trying to dump some tables from a 9.5 beta 2 to a 9.4.4 server. The form of the command I am using is the fairly standard:

pg_dump -t table dbname | psql -h hostname -d dbname

I am using the Postgres user, which I realize is probably not ideal, but as these are both dev data processing boxes that only I use and it is relevant to the error which follows. Initially, I got the error

ERROR: unrecognized configuration parameter "row_security"

which is expected as this feature is new in 9.5, and, yes, I realize that it is not advisable to pg_dump between different Postgres versions, but, sadly unavoidable, given that I have hit a very obscure bug, and need to move a large amount of static data once.

So, using this hideous hack to remove the row_security error, and turning on stop on first error:

pg_dump -t tablemame dbname |sed 's/SET row_security = off;//'|
psql -v ON_ERROR_STOP=1 -h hostname -d dbname

I now get:

ERROR: permission denied to create "pg_catalog.tablename" DETAIL: System catalog modifications are currently disallowed

While it may be undesirable to use the Postgres user/role, my understanding is that there should be no permission issues of this nature. I am open to any suggestions bearing in mind that this is a one off operation and that these are data processing dev boxes not connected to any live services, so expediency preferred over ideal corporate data access policies. Having said that, it would be good to understand how to do this properly and avoid this in future.