Обсуждение: Incorrect syntax when restoring a single table
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)
$ 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)
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 ?
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
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.
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_y2022m08That 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.