I have a table named test with id column. And id column has updated by sequence called "test_id_seq". This test table was created by the user "A" and another user "B" have the read permission. While creating the dump for table from the user "B". Using the following comment
pg_dump -U B -t test rafiu > test.sql
it showing the error like
pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation test_id_seq
Is there any option to dump only the table?
When the sequence is "owned" by a column, it's dumped with it, so the user producing the dump must have the rights to access it (GRANT SELECT ON sequence_name TO username)
This happens when using the SERIAL/BIGSERIAL pseudo-datatypes. However, the sequence can still be detached from the column afterwards by issuing:
ALTER SEQUENCE test_id_seq OWNED BY none;
After that, assigning a default value for test.ID with the sequence will continue to work as usual, but pg_dump will make no attempt to dump the sequence with the table.
If the table is created from the start with a pre-existing sequence (not using SERIAL), then the outcome is the same without the need for ALTER SEQUENCE.
Example:
create sequence seq1;
create table test1 (id int default nextval('seq1'));
In this case the table would be dumped with pg_dump -t test1 as:
CREATE TABLE test1 (
id integer DEFAULT nextval('seq1'::regclass)
);
with no other reference to seq1 and no need for permission to read it.
Have you tried using the -T option to neglect the sequence?
pg_dump -U B -T test_id_seq rafiu > test.sql
This should take a pgdump without that sequence.
pg_dump from PostgreSQL 9.4)
