pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump weirdness
7+ messages / 5 participants
[nested] [flat]
* pg_dump weirdness
@ 2021-11-05 19:10 John Scalia <jayknowsunix@gmail.com>
2021-11-05 20:59 ` Re: pg_dump weirdness MichaelDBA@sqlexec.com <michaeldba@sqlexec.com>
2021-11-06 13:25 ` Re: pg_dump weirdness MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 2 replies; 7+ messages in thread
From: John Scalia @ 2021-11-05 19:10 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump weirdness
2021-11-05 19:10 pg_dump weirdness John Scalia <jayknowsunix@gmail.com>
@ 2021-11-05 20:59 ` MichaelDBA@sqlexec.com <michaeldba@sqlexec.com>
2021-11-06 00:10 ` Re: pg_dump weirdness Ron <ronljohnsonjr@gmail.com>
2021-11-06 00:47 ` Re: pg_dump weirdness Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 7+ messages in thread
From: MichaelDBA@sqlexec.com @ 2021-11-05 20:59 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
You cannot use a newer version of pgdump to export and then insert it into an older version of PostgreSQL if I am understanding you 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 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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump weirdness
2021-11-05 19:10 pg_dump weirdness John Scalia <jayknowsunix@gmail.com>
2021-11-05 20:59 ` Re: pg_dump weirdness MichaelDBA@sqlexec.com <michaeldba@sqlexec.com>
@ 2021-11-06 00:10 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Ron @ 2021-11-06 00:10 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 understanding you 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 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
>>
>>
>>
>
>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump weirdness
2021-11-05 19:10 pg_dump weirdness John Scalia <jayknowsunix@gmail.com>
2021-11-05 20:59 ` Re: pg_dump weirdness MichaelDBA@sqlexec.com <michaeldba@sqlexec.com>
@ 2021-11-06 00:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-11-06 02:41 ` Re: pg_dump weirdness John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2021-11-06 00:47 UTC (permalink / raw)
To: MichaelDBA@sqlexec.com <michaeldba@sqlexec.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; pgsql-admin@lists.postgresql.org
"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 understanding you 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump weirdness
2021-11-05 19:10 pg_dump weirdness John Scalia <jayknowsunix@gmail.com>
2021-11-05 20:59 ` Re: pg_dump weirdness MichaelDBA@sqlexec.com <michaeldba@sqlexec.com>
2021-11-06 00:47 ` Re: pg_dump weirdness Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-11-06 02:41 ` John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: John Scalia @ 2021-11-06 02:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: michaeldba@sqlexec.com; pgsql-admin@lists.postgresql.org
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 understanding you 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump weirdness
2021-11-05 19:10 pg_dump weirdness John Scalia <jayknowsunix@gmail.com>
@ 2021-11-06 13:25 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-11-08 14:38 ` Re: pg_dump weirdness John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: MichaelDBA @ 2021-11-06 13:25 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump weirdness
2021-11-05 19:10 pg_dump weirdness John Scalia <jayknowsunix@gmail.com>
2021-11-06 13:25 ` Re: pg_dump weirdness MichaelDBA <MichaelDBA@sqlexec.com>
@ 2021-11-08 14:38 ` John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: John Scalia @ 2021-11-08 14:38 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
On 11/6/2021 9:25 AM, MichaelDBA wrote:
> 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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-11-08 14:38 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-11-05 19:10 pg_dump weirdness John Scalia <jayknowsunix@gmail.com>
2021-11-05 20:59 ` MichaelDBA@sqlexec.com <michaeldba@sqlexec.com>
2021-11-06 00:10 ` Ron <ronljohnsonjr@gmail.com>
2021-11-06 00:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-11-06 02:41 ` John Scalia <jayknowsunix@gmail.com>
2021-11-06 13:25 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-11-08 14:38 ` John Scalia <jayknowsunix@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox