agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedIncorrect syntax when restoring a single table
5+ messages / 4 participants
[nested] [flat]
* Incorrect syntax when restoring a single table
@ 2024-06-27 14:01 Ron Johnson <ronljohnsonjr@gmail.com>
2024-06-27 15:01 ` Re: Incorrect syntax when restoring a single table Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
2024-06-27 15:05 ` Re: Incorrect syntax when restoring a single table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Ron Johnson @ 2024-06-27 14:01 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
PG 16.3
Obviously I've overlooked something, but can't see what it is.
DB name is "tap".
pwd is the directory above where the directory-format backup is located.
I'm trying to restore the single table tapschema.batch_rp4_y2022m08 to the
existing database named "tap".
The (compressed) backup file is about a megabyte, so there's data in it.
However, when I run the "pg_restore -t" command, it connects, but doesn't
restore anything.
$ pg_restore -l -Fd -f tap.list tap
$ grep " batch_rp4_y2022m08 " tap.list
2118; 1259 1814433 TABLE tapschema batch_rp4_y2022m08 fis_tap
128513; 0 0 TABLE ATTACH tapschema batch_rp4_y2022m08 fis_tap
847489; 0 1814433 TABLE DATA tapschema batch_rp4_y2022m08 fis_tap
$ dir tap/847489*
-rw-r--r-- 1 postgres postgres 1067185 2024-06-27 02:31:24 tap/847489.dat.gz
$ pg_restore -v -a -t tapschema.batch_rp4_y2022m08 -Fd --dbname=tap tap
pg_restore: connecting to database for restore
$ psql tap -c "select count(*) from batch_rp4_y2022m08;"
count
-------
0
(1 row)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incorrect syntax when restoring a single table
2024-06-27 14:01 Incorrect syntax when restoring a single table Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-06-27 15:01 ` Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Achilleas Mantzios - cloud @ 2024-06-27 15:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 6/27/24 17:01, Ron Johnson wrote:
> PG 16.3
>
> Obviously I've overlooked something, but can't see what it is.
>
> DB name is "tap".
> pwd is the directory above where the directory-format backup is located.
>
> I'm trying to restore the single table tapschema.batch_rp4_y2022m08 to
> the existing database named "tap".
>
> The (compressed) backup file is about a megabyte, so there's data in
> it. However, when I run the "pg_restore -t" command, it connects, but
> doesn't restore anything.
>
> $ pg_restore -l -Fd -f tap.list tap
>
> $ grep " batch_rp4_y2022m08 " tap.list
> 2118; 1259 1814433 TABLE tapschema batch_rp4_y2022m08 fis_tap
> 128513; 0 0 TABLE ATTACH tapschema batch_rp4_y2022m08 fis_tap
> 847489; 0 1814433 TABLE DATA tapschema batch_rp4_y2022m08 fis_tap
>
> $ dir tap/847489*
> -rw-r--r-- 1 postgres postgres 1067185 2024-06-27 02:31:24
> tap/847489.dat.gz
>
> $ pg_restore -v -a -t tapschema.batch_rp4_y2022m08 -Fd --dbname=tap tap
> pg_restore: connecting to database for restore
>
> $ psql tap -c "select count(*) from batch_rp4_y2022m08;"
> count
> -------
> 0
> (1 row)
Is your search_path correct ?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incorrect syntax when restoring a single table
2024-06-27 14:01 Incorrect syntax when restoring a single table Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-06-27 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-06-27 15:23 ` Re: Incorrect syntax when restoring a single table Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2024-06-27 15:05 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> $ pg_restore -v -a -t tapschema.batch_rp4_y2022m08 -Fd --dbname=tap tap
I think what you have to do is write -n tapschema -t batch_rp4_y2022m08
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incorrect syntax when restoring a single table
2024-06-27 14:01 Incorrect syntax when restoring a single table Ron Johnson <ronljohnsonjr@gmail.com>
2024-06-27 15:05 ` Re: Incorrect syntax when restoring a single table Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-06-27 15:23 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-06-27 15:40 ` Re: Incorrect syntax when restoring a single table David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2024-06-27 15:23 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, Jun 27, 2024 at 11:05 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ron Johnson <ronljohnsonjr@gmail.com> writes:
> > $ pg_restore -v -a -t tapschema.batch_rp4_y2022m08 -Fd --dbname=tap tap
>
> I think what you have to do is write -n tapschema -t batch_rp4_y2022m08
>
That indeed is the solution. Should have seen it in the docs.
pg_restore's use of "-t" is inconsistent with other utilities (pg_dump,
vacuumdb, reindexdb, clusterdb). That seems to be a design bug.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incorrect syntax when restoring a single table
2024-06-27 14:01 Incorrect syntax when restoring a single table Ron Johnson <ronljohnsonjr@gmail.com>
2024-06-27 15:05 ` Re: Incorrect syntax when restoring a single table Tom Lane <tgl@sss.pgh.pa.us>
2024-06-27 15:23 ` Re: Incorrect syntax when restoring a single table Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-06-27 15:40 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2024-06-27 15:40 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thursday, June 27, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Thu, Jun 27, 2024 at 11:05 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Ron Johnson <ronljohnsonjr@gmail.com> writes:
>> > $ pg_restore -v -a -t tapschema.batch_rp4_y2022m08 -Fd --dbname=tap tap
>>
>> I think what you have to do is write -n tapschema -t batch_rp4_y2022m08
>>
>
> That indeed is the solution. Should have seen it in the docs.
>
> pg_restore's use of "-t" is inconsistent with other utilities (pg_dump,
> vacuumdb, reindexdb, clusterdb). That seems to be a design bug.
>
>
I suppose writing the extra matching logic for in-file matching was more
difficult than doing the same when the data sits in catalogs and you have a
regexp operator. Less a bug and more a decision of convenience I suppose.
No pressure from me to change it.
On a related note, the third tip refers to an out-of-support version.
Shouldn’t we do away with it?
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-06-27 15:40 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-27 14:01 Incorrect syntax when restoring a single table Ron Johnson <ronljohnsonjr@gmail.com>
2024-06-27 15:01 ` Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
2024-06-27 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-06-27 15:23 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-06-27 15:40 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox