Обсуждение: pg_dump weirdness

I ran a pg_dump for a colleague, as he needs to move some data into a 
new database. I ran the command as:

pg_dump -h original_server -t original_schema.table_name -U user -W 
dbname > table_name.sql

after the dump was completed with no errors, I went into the target 
instance, and truncated the target table. I also had to do a sed 
operation to change the schema name in the dump file for the schema in 
the target. I verified that sed properly modified the file. Now, after 
all this, I used:

psql -h target_server -U user newdbname < table_name.sql

This loaded with a handful of warnings, like table already exists, the 
sequence already exists, and some privileges could not be set. After 
those warnings it ran for more than 1/2 hour to load the table. The 
problem is that after this was all done. I went back into the target 
instance, and checked the newly loaded table. There was nothing in it, 
i.e., 0 rows.

The pg_dump and the output file claim the pg_dump version was 12.6, 
while the server it ran against was 11.9. Both instances are AWS RDS 
Aurora-PostgreSQL ones. Any ideas why there was no data in the table? 
Other than on inputting it using psql, there were no other errors.

--jay

You cannot use a newer version of pgdump to export and then insert it into an older version of PostgreSQL if I am
understandingyou correctly. 

Sent from my iPhone

> On Nov 5, 2021, at 3:10 PM, John Scalia <jayknowsunix@gmail.com> wrote:
>
> I ran a pg_dump for a colleague, as he needs to move some data into a new database. I ran the command as:
>
> pg_dump -h original_server -t original_schema.table_name -U user -W dbname > table_name.sql
>
> after the dump was completed with no errors, I went into the target instance, and truncated the target table. I also
hadto do a sed operation to change the schema name in the dump file for the schema in the target. I verified that sed
properlymodified the file. Now, after all this, I used: 
>
> psql -h target_server -U user newdbname < table_name.sql
>
> This loaded with a handful of warnings, like table already exists, the sequence already exists, and some privileges
couldnot be set. After those warnings it ran for more than 1/2 hour to load the table. The problem is that after this
wasall done. I went back into the target instance, and checked the newly loaded table. There was nothing in it, i.e., 0
rows.
>
> The pg_dump and the output file claim the pg_dump version was 12.6, while the server it ran against was 11.9. Both
instancesare AWS RDS Aurora-PostgreSQL ones. Any ideas why there was no data in the table? Other than on inputting it
usingpsql, there were no other errors. 
>
> --jay
>
>
>

That's correct.  It's backward compatible, not forward compatible.

On 11/5/21 3:59 PM, MichaelDBA@sqlexec.com wrote:
> You cannot use a newer version of pgdump to export and then insert it into an older version of PostgreSQL if I am
understandingyou correctly.
 
>
> Sent from my iPhone
>
>> On Nov 5, 2021, at 3:10 PM, John Scalia <jayknowsunix@gmail.com> wrote:
>>
>> I ran a pg_dump for a colleague, as he needs to move some data into a new database. I ran the command as:
>>
>> pg_dump -h original_server -t original_schema.table_name -U user -W dbname > table_name.sql
>>
>> after the dump was completed with no errors, I went into the target instance, and truncated the target table. I also
hadto do a sed operation to change the schema name in the dump file for the schema in the target. I verified that sed
properlymodified the file. Now, after all this, I used:
 
>>
>> psql -h target_server -U user newdbname < table_name.sql
>>
>> This loaded with a handful of warnings, like table already exists, the sequence already exists, and some privileges
couldnot be set. After those warnings it ran for more than 1/2 hour to load the table. The problem is that after this
wasall done. I went back into the target instance, and checked the newly loaded table. There was nothing in it, i.e., 0
rows.
>>
>> The pg_dump and the output file claim the pg_dump version was 12.6, while the server it ran against was 11.9. Both
instancesare AWS RDS Aurora-PostgreSQL ones. Any ideas why there was no data in the table? Other than on inputting it
usingpsql, there were no other errors.
 
>>
>> --jay
>>
>>
>>
>
>

-- 
Angular momentum makes the world go 'round.

"MichaelDBA@sqlexec.com" <michaeldba@sqlexec.com> writes:
> You cannot use a newer version of pgdump to export and then insert it into an older version of PostgreSQL if I am
understandingyou correctly. 

Well, you can try.  You might get errors from DDL syntax the older server
doesn't understand, but usually that won't happen if the dump is taken
from a server of that vintage or older.  We don't promise anything, but
I'd expect it to usually work.

I'm a little suspicious of the OP's claim that there were only warnings
and not errors while trying to restore the dump -- an error would go a
long way towards explaining the report.  In any case, we'd need to see
all those warnings to have much chance of diagnosing this.

            regards, tom lane

I’ll capture the warnings on Monday and share them here, but I honestly saw nothing that should have broken the load.
Plus,no one has mentioned why the restore took over 1/2 hour but nothing was loaded. 

Sent from my iPhone

> On Nov 5, 2021, at 20:47, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "MichaelDBA@sqlexec.com" <michaeldba@sqlexec.com> writes:
>> You cannot use a newer version of pgdump to export and then insert it into an older version of PostgreSQL if I am
understandingyou correctly. 
>
> Well, you can try.  You might get errors from DDL syntax the older server
> doesn't understand, but usually that won't happen if the dump is taken
> from a server of that vintage or older.  We don't promise anything, but
> I'd expect it to usually work.
>
> I'm a little suspicious of the OP's claim that there were only warnings
> and not errors while trying to restore the dump -- an error would go a
> long way towards explaining the report.  In any case, we'd need to see
> all those warnings to have much chance of diagnosing this.
>
>            regards, tom lane

I think you are pointing to the wrong binaries.

Use the absolute path to your pg_dump binary to make sure you are using the version you think you are using.  For example:
/usr/pgsql-11/bin/pg_dump -h original_server -t original_schema.table_name -U user -W dbname > table_name.sql

And for the target do the same for psql plus add the stop on error parm:
/usr/pgsql-11/bin/psql -h target_server -U user newdbname -v ON_ERROR_STOP=1 < table_name.sql

Regards,
Michael Vitale

I determined what the problem was this morning, there was one error about a foreign key violation, which I did not really see earlier. so I removed two foreign key constraints from the target table. The load then worked properly, and some 600k records are now loaded.

I think you are pointing to the wrong binaries.

Use the absolute path to your pg_dump binary to make sure you are using the version you think you are using.  For example:
/usr/pgsql-11/bin/pg_dump -h original_server -t original_schema.table_name -U user -W dbname > table_name.sql

And for the target do the same for psql plus add the stop on error parm:
/usr/pgsql-11/bin/psql -h target_server -U user newdbname -v ON_ERROR_STOP=1 < table_name.sql

Regards,
Michael Vitale