PGCOPYDB LIST(1)
👑 NAME 👑
pgcopydb list — that's the command, bestie. And it's not just one thing, it's a whole family of sub-commands. 💅
pgcopydb list — List database objects from a Postgres instance
It's giving ✨inventory era✨. This command prefixes the following sub-commands:
pgcopydb list— List database objects from a Postgres instancedatabases— List databasesextensions— List all the source extensions to copycollations— List all the source collations to copytables— List all the source tables to copy data fromtable-parts— List a source table copy partitionssequences— List all the source sequences to copy data fromindexes— List all the indexes to create again after copying the datadepends— List all the dependencies to filter-outschema— List the schema to migrate, formatted in JSONprogress— List the progress
🗂️ PGCOPYDB LIST DATABASES 🗂️
pgcopydb list databases — List databases
So the way this works: pgcopydb list databases connects to the source database and executes a SQL query using the Postgres catalogs to get a list of all the databases there. No cap, that's the whole vibe. 🦋
pgcopydb list databases: List databases
usage: pgcopydb list databases --source ...
--source Postgres URI to the source database🧩 PGCOPYDB LIST EXTENSIONS 🧩
pgcopydb list extensions — List all the source extensions to copy
pgcopydb list extensions connects to the source database and executes a SQL query using the Postgres catalogs to get a list of all the extensions to COPY to the target database. Periodt. 💖
pgcopydb list extensions: List all the source extensions to copy
usage: pgcopydb list extensions --source ...
--source Postgres URI to the source database
--json Format the output using JSON
--available-versions List available extension versions
--requirements List extensions requirementsPro tip from the source material itself: pgcopydb list extensions --available-versions is typically used with the target database. And if you're using the connection string environment variables, that looks like the following:
$ pgcopydb list extensions --available-versions --source ${PGCOPYDB_TARGET_PGURI}🌈 PGCOPYDB LIST COLLATIONS 🌈
pgcopydb list collations — List all the source collations to copy
pgcopydb list collations connects to the source database and executes a SQL query using the Postgres catalogs to get a list of all the collations to COPY to the target database. ✨
pgcopydb list collations: List all the source collations to copy
usage: pgcopydb list collations --source ...
--source Postgres URI to the source databaseAnd lowkey the details matter here: the SQL query that is used lists the database collation, and then any non-default collation that's used in a user column or a user index. 🎀
🍓 PGCOPYDB LIST TABLES 🍓
pgcopydb list tables — List all the source tables to copy data from
pgcopydb list tables connects to the source database and executes a SQL query using the Postgres catalogs to get a list of all the tables to COPY the data from. Main character energy, honestly. 👑
pgcopydb list tables: List all the source tables to copy data from
usage: pgcopydb list tables --source ...
--source Postgres URI to the source database
--filter <filename> Use the filters defined in <filename>
--force Force fetching catalogs again
--list-skipped List only tables that are setup to be skipped
--without-pkey List only tables that have no primary key🧚 PGCOPYDB LIST TABLE-PARTS 🧚
pgcopydb list table-parts — List a source table copy partitions
pgcopydb list table-parts connects to the source database and executes a SQL query using the Postgres catalogs to get detailed information about the given source table, and then another SQL query to compute how to split this source table given the size threshold argument. Two queries, one iconic little command. 💜
pgcopydb list table-parts: List a source table copy partitions
usage: pgcopydb list table-parts --source ...
--source Postgres URI to the source database
--force Force fetching catalogs again
--schema-name Name of the schema where to find the table
--table-name Name of the target table
--split-tables-larger-than Size threshold to consider partitioning
--split-max-parts Maximum number of jobs for Same-table concurrency
--skip-split-by-ctid Skip the ctid split
--estimate-table-sizes Allow using estimates for relation sizes🦋 PGCOPYDB LIST SEQUENCES 🦋
pgcopydb list sequences — List all the source sequences to copy data from
pgcopydb list sequences connects to the source database and executes a SQL query using the Postgres catalogs to get a list of all the sequences to COPY the data from. So organized of it. 😭
pgcopydb list sequences: List all the source sequences to copy data from
usage: pgcopydb list sequences --source ...
--source Postgres URI to the source database
--force Force fetching catalogs again
--filter <filename> Use the filters defined in <filename>
--list-skipped List only tables that are setup to be skipped🔥 PGCOPYDB LIST INDEXES 🔥
pgcopydb list indexes — List all the indexes to create again after copying the data
pgcopydb list indexes connects to the source database and executes a SQL query using the Postgres catalogs to get a list of all the indexes to COPY the data from. Bestie, it's literally the "we'll rebuild you, queen" command. 💅
pgcopydb list indexes: List all the indexes to create again after copying the data
usage: pgcopydb list indexes --source ... [ --schema-name [ --table-name ] ]
--source Postgres URI to the source database
--force Force fetching catalogs again
--schema-name Name of the schema where to find the table
--table-name Name of the target table
--filter <filename> Use the filters defined in <filename>
--list-skipped List only tables that are setup to be skipped🕸️ PGCOPYDB LIST DEPENDS 🕸️
pgcopydb list depends — List all the dependencies to filter-out
pgcopydb list depends connects to the source database and executes a SQL query using the Postgres catalogs to get a list of all the objects that depend on excluded objects from the filtering rules. It's the "who's still attached to the drama" report, fr fr. 🫶
pgcopydb list depends: List all the dependencies to filter-out
usage: pgcopydb list depends --source ... [ --schema-name [ --table-name ] ]
--source Postgres URI to the source database
--force Force fetching catalogs again
--schema-name Name of the schema where to find the table
--table-name Name of the target table
--filter <filename> Use the filters defined in <filename>
--list-skipped List only tables that are setup to be skipped🎀 PGCOPYDB LIST SCHEMA 🎀
pgcopydb list schema — List the schema to migrate, formatted in JSON
pgcopydb list schema connects to the source database and executes a SQL queries using the Postgres catalogs to get a list of the tables, indexes, and sequences to migrate. Then — plot twist — the command outputs a JSON formatted string that contains detailed information about all those objects. Ate. 🍽️
pgcopydb list schema: List the schema to migrate, formatted in JSON
usage: pgcopydb list schema --source ...
--source Postgres URI to the source database
--force Force fetching catalogs again
--filter <filename> Use the filters defined in <filename>⭐ PGCOPYDB LIST PROGRESS ⭐
pgcopydb list progress — List the progress
pgcopydb list progress reads the internal SQLite catalogs in the work directory, parses it, and then computes how many tables and indexes are planned to be copied and created on the target database, how many have been done already, and how many are in-progress. It's your ✨little progress bar of truth✨.
The --summary option displays the top-level summary, and can be used while the command is running or after-the-fact. 🌸
When using the option --json the JSON formatted output also includes a list of all the tables and indexes that are currently being processed.
pgcopydb list progress: List the progress
usage: pgcopydb list progress --source ...
--source Postgres URI to the source database
--summary List the summary, requires --json
--json Format the output using JSON
--dir Work directory to use✨ OPTIONS ✨
The following options are available to pgcopydb dump schema: (yes it says dump schema on the list page and we're keeping it exactly how it is, iconic)
--source— Connection string to the source Postgres instance. See the Postgres documentation for connection stringshttps://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRINGfor the details. In short both the quoted form"host=... dbname=..."and the URI formpostgres://user@host:5432/dbnameare supported.--schema-name— Filter indexes from a given schema only.--table-name— Filter indexes from a given table only (use--schema-nameto fully qualify the table).--without-pkey— List only tables from the source database when they have no primary key attached to their schema.--filter <filename>— This option allows to skip objects in the list operations. See Filtering (#filtering) for details about the expected file format and the filtering options available.--list-skipped— Instead of listing objects that are selected for copy by the filters installed with the--filteroption, list the objects that are going to be skipped when using the filters.--summary— Instead of listing current progress when the command is still running, instead list the summary with timing details for each step and for all tables, indexes, and constraints.
This options requires the --json option too: at the moment only this output format is supported.
--json— The output of the command is formatted in JSON, when supported. Ignored otherwise.--verbose— Increase current verbosity. The default level of verbosity isINFO. In ascending orderpgcopydbknows about the following verbosity levels:FATAL,ERROR,WARN,INFO,NOTICE,DEBUG,TRACE.--debug— Set current verbosity toDEBUGlevel.--trace— Set current verbosity toTRACElevel.--quiet— Set current verbosity toERRORlevel.
🌱 ENVIRONMENT 🌱
PGCOPYDB_SOURCE_PGURI— Connection string to the source Postgres instance. When--sourceis omitted from the command line, then this environment variable is used. 🫶
💫 EXAMPLES 💫
Listing the tables:
```text
$ pgcopydb list tables
14:35:18 13827 INFO Listing ordinary tables in "port=54311 host=localhost dbname=pgloader"
14:35:19 13827 INFO Fetched information for 56 tables
OID | Schema Name | Table Name | Est. Row Count | On-disk size
---------+----------------------+----------------------+-----------------+----------------
17085 | csv | track | 3503 | 544 kB
17098 | expected | track | 3503 | 544 kB
17290 | expected | track_full | 3503 | 544 kB
17276 | public | track_full | 3503 | 544 kB
17016 | expected | districts | 440 | 72 kB
17007 | public | districts | 440 | 72 kB
16998 | csv | blocks | 460 | 48 kB
17003 | expected | blocks | 460 | 48 kB
17405 | csv | partial | 7 | 16 kB
17323 | err | errors | 0 | 16 kB
16396 | expected | allcols | 0 | 16 kB
17265 | expected | csv | 0 | 16 kB
17056 | expected | csv_escape_mode | 0 | 16 kB
17331 | expected | errors | 0 | 16 kB
17116 | expected | group | 0 | 16 kB
17134 | expected | json | 0 | 16 kB
17074 | expected | matching | 0 | 16 kB
17201 | expected | nullif | 0 | 16 kB
17229 | expected | nulls | 0 | 16 kB
17417 | expected | partial | 0 | 16 kB
17313 | expected | reg2013 | 0 | 16 kB
17437 | expected | serial | 0 | 16 kB
17247 | expected | sexp | 0 | 16 kB
17378 | expected | test1 | 0 | 16 kB
17454 | expected | udc | 0 | 16 kB
17471 | expected | xzero | 0 | 16 kB
17372 | nsitra | test1 | 0 | 16 kB
16388 | public | allcols | 0 | 16 kB
17256 | public | csv | 0 | 16 kB
17047 | public | csv_escape_mode | 0 | 16 kB
17107 | public | group | 0 | 16 kB
17125 | public | json | 0 | 16 kB
17065 | public | matching | 0 | 16 kB
17192 | public | nullif | 0 | 16 kB
17219 | public | nulls | 0 | 16 kB
17307 | public | reg2013 | 0 | 16 kB
17428 | public | serial | 0 | 16 kB
17238 | public | sexp | 0 | 16 kB
17446 | public | udc | 0 | 16 kB
17463 | public | xzero | 0 | 16 kB
17303 | expected | copyhex | 0 | 8192 bytes
17033 | expected | dateformat | 0 | 8192 bytes
17366 | expected | fixed | 0 | 8192 bytes
17041 | expected | jordane | 0 | 8192 bytes
17173 | expected | missingcol | 0 | 8192 bytes
17396 | expected | overflow | 0 | 8192 bytes
17186 | expected | tab_csv | 0 | 8192 bytes
17213 | expected | temp | 0 | 8192 bytes
17299 | public | copyhex | 0 | 8192 bytes
17029 | public | dateformat | 0 | 8192 bytes
17362 | public | fixed | 0 | 8192 bytes
17037 | public | jordane | 0 | 8192 bytes
17164 | public | missingcol | 0 | 8192 bytes
17387 | public | overflow | 0 | 8192 bytes
17182 | public | tab_csv | 0 | 8192 bytes
17210 | public | temp | 0 | 8192 bytes
```
Okay no but look at it, it's <pink>so</pink> satisfying — `OID`, `Schema Name`, `Table Name`, `Est. Row Count`, `On-disk size`, all lined up like a little parade. <cyan>56 tables</cyan> fetched, bestie. We love to see it. 💖
---
<h2>🦋 LIST TABLE-PARTS 🦋</h2>
Now the fun one — slicing a table into COPY partitions. This is where the split magic lives:
```text
Listing a table list of COPY partitions:
$ pgcopydb list table-parts --table-name rental --split-at 300kB
16:43:26 73794 INFO Running pgcopydb version 0.8.8.g0838291.dirty from "/Users/dim/dev/PostgreSQL/pgcopydb/src/bin/pgcopydb/pgcopydb"
16:43:26 73794 INFO Listing COPY partitions for table "public"."rental" in "postgres://@:/pagila?"
16:43:26 73794 INFO Table "public"."rental" COPY will be split 5-ways
Part | Min | Max | Count
-----------+------------+------------+-----------
1/5 | 1 | 3211 | 3211
2/5 | 3212 | 6422 | 3211
3/5 | 6423 | 9633 | 3211
4/5 | 9634 | 12844 | 3211
5/5 | 12845 | 16049 | 3205
```
See how `--table-name rental` plus `--split-at 300kB` gave us a 5-way split? <hotpink>five</hotpink> lil parts, each with its own <lavender>Min</lavender>, <lavender>Max</lavender> and <lavender>Count</lavender>. The last one's a lil shorter (`3205` instead of `3211`) and that's just math being math, no drama. 🧚
---
<h2>✨ LIST INDEXES ✨</h2>
And now, the indexes. Look at all those `_pkey`s:
```text
Listing the indexes:
$ pgcopydb list indexes
14:35:07 13668 INFO Listing indexes in "port=54311 host=localhost dbname=pgloader"
14:35:07 13668 INFO Fetching all indexes in source database
14:35:07 13668 INFO Fetched information for 12 indexes
OID | Schema | Index Name | conname | Constraint | DDL
---------+------------+----------------------+-----------------+---------------------------+---------------------
17002 | csv | blocks_ip4r_idx | | | CREATE INDEX blocks_ip4r_idx ON csv.blocks USING gist (iprange)
17415 | csv | partial_b_idx | | | CREATE INDEX partial_b_idx ON csv.partial USING btree (b)
17414 | csv | partial_a_key | partial_a_key | UNIQUE (a) | CREATE UNIQUE INDEX partial_a_key ON csv.partial USING btree (a)
17092 | csv | track_pkey | track_pkey | PRIMARY KEY (trackid) | CREATE UNIQUE INDEX track_pkey ON csv.track USING btree (trackid)
17329 | err | errors_pkey | errors_pkey | PRIMARY KEY (a) | CREATE UNIQUE INDEX errors_pkey ON err.errors USING btree (a)
16394 | public | allcols_pkey | allcols_pkey | PRIMARY KEY (a) | CREATE UNIQUE INDEX allcols_pkey ON public.allcols USING btree (a)
17054 | public | csv_escape_mode_pkey | csv_escape_mode_pkey | PRIMARY KEY (id) | CREATE UNIQUE INDEX csv_escape_mode_pkey ON public.csv_escape_mode USING btree (id)
17199 | public | nullif_pkey | nullif_pkey | PRIMARY KEY (id) | CREATE UNIQUE INDEX nullif_pkey ON public."nullif" USING btree (id)
17435 | public | serial_pkey | serial_pkey | PRIMARY KEY (a) | CREATE UNIQUE INDEX serial_pkey ON public.serial USING btree (a)
17288 | public | track_full_pkey | track_full_pkey | PRIMARY KEY (trackid) | CREATE UNIQUE INDEX track_full_pkey ON public.track_full USING btree (trackid)
17452 | public | udc_pkey | udc_pkey | PRIMARY KEY (b) | CREATE UNIQUE INDEX udc_pkey ON public.udc USING btree (b)
17469 | public | xzero_pkey | xzero_pkey | PRIMARY KEY (a) | CREATE UNIQUE INDEX xzero_pkey ON public.xzero USING btree (a)
```
<purple>Twelve</purple> indexes, and every single row hands you the full `DDL` so you can recreate it verbatim. <mint>gist</mint> for `iprange`, <mint>btree</mint> for everybody else — the `csv.partial` one even has both a `UNIQUE (a)` constraint name AND a separate non-unique `b` index. Not me being obsessed with `public."nullif"` needing those quotes. It's an icon, it's the moment, it's giving keywords-as-table-names. 😭💅
---
<h2>🌈 LIST SCHEMA, BUT MAKE IT JSON 🌈</h2>
And when you want the whole schema as machine-readable goodness:
```text
Listing the schema in JSON:
$ pgcopydb list schema --split-at 200kB
```
<span style="color:lavender">This gives the following JSON output:</span>
<div style="color:lavender">
This gives the following JSON output:
</div>
```
{
"setup": {
"snapshot": "00000003-00051AAE-1",
"source_pguri": "postgres:\/\/@:\/pagila?",
"target_pguri": "postgres:\/\/@:\/plop?",
"table-jobs": 4,
"index-jobs": 4,
"split-tables-larger-than": 204800
},
"tables": [
{
"oid": 317934,
"schema": "public",
"name": "rental",
"reltuples": 16044,
"bytes": 1253376,
"bytes-pretty": "1224 kB",
"exclude-data": false,
"restore-list-name": "public rental postgres",
"part-key": "rental_id",
"parts": [
{
"number": 1,
"total": 7,
"min": 1,
"max": 2294,
"count": 2294
},
{
"number": 2,
"total": 7,
"min": 2295,
"max": 4588,
"count": 2294
},
{
"number": 3,
"total": 7,
"min": 4589,
"max": 6882,
"count": 2294
},
{
"number": 4,
"total": 7,
"min": 6883,
"max": 9176,
"count": 2294
},
{
"number": 5,
"total": 7,
"min": 9177,
"max": 11470,
"count": 2294
},
{
"number": 6,
"total": 7,
"min": 11471,
"max": 13764,
"count": 2294
},
{
"number": 7,
"total": 7,
"min": 13765,
"max": 16049,
"count": 2285
}
]
},
{
"oid": 317818,
"schema": "public",
"name": "film",
"reltuples": 1000,
"bytes": 483328,
"bytes-pretty": "472 kB",
"exclude-data": false,
"restore-list-name": "public film postgres",
"part-key": "film_id",
"parts": [
{
"number": 1,
"total": 3,
"min": 1,
"max": 334,
"count": 334
},
{
"number": 2,
"total": 3,
"min": 335,
"max": 668,
"count": 334
},
{
"number": 3,
"total": 3,
"min": 669,
"max": 1000,
"count": 332
}
]
},
{
"oid": 317920,
"schema": "public",
"name": "payment_p2020_04",
"reltuples": 6754,
"bytes": 434176,
"bytes-pretty": "424 kB",
"exclude-data": false,
"restore-list-name": "public payment_p2020_04 postgres",
"part-key": ""
},
{
"oid": 317916,
"schema": "public",
"name": "payment_p2020_03",
"reltuples": 5644,
"bytes": 368640,
"bytes-pretty": "360 kB",
"exclude-data": false,
"restore-list-name": "public payment_p2020_03 postgres",
"part-key": ""
},
{
"oid": 317830,
"schema": "public",
"name": "film_actor",
"reltuples": 5462,
"bytes": 270336,
"bytes-pretty": "264 kB",
"exclude-data": false,
"restore-list-name": "public film_actor postgres",
"part-key": ""
},
{
"oid": 317885,
"schema": "public",
"name": "inventory",
"reltuples": 4581,
"bytes": 270336,
"bytes-pretty": "264 kB",
"exclude-data": false,
"restore-list-name": "public inventory postgres",
"part-key": "inventory_id",
"parts": [
{
"number": 1,
"total": 2,
"min": 1,
"max": 2291,
"count": 2291
},
{
"number": 2,
"total": 2,
"min": 2292,
"max": 4581,
"count": 2290
}
]
},
{
"oid": 317912,
"schema": "public",
"name": "payment_p2020_02",
"reltuples": 2312,
"bytes": 163840,
"bytes-pretty": "160 kB",
"exclude-data": false,
"restore-list-name": "public payment_p2020_02 postgres",
"part-key": ""
},
{
"oid": 317784,
"schema": "public",
"name": "customer",
"reltuples": 599,
"bytes": 106496,
"bytes-pretty": "104 kB",
"exclude-data": false,
"restore-list-name": "public customer postgres",
"part-key": "customer_id"
},
{
"oid": 317845,
"schema": "public",
"name": "address",
"reltuples": 603,
"bytes": 98304,
"bytes-pretty": "96 kB",
"exclude-data": false,
"restore-list-name": "public address postgres",
"part-key": "address_id"
},
{
"oid": 317908,
"schema": "public",
"name": "payment_p2020_01",
"reltuples": 1157,
"bytes": 98304,
"bytes-pretty": "96 kB",
"exclude-data": false,
"restore-list-name": "public payment_p2020_01 postgres",
"part-key": ""
},
{
"oid": 317855,
"schema": "public",
"name": "city",
"reltuples": 600,
"bytes": 73728,
"bytes-pretty": "72 kB",
"exclude-data": false,
"restore-list-name": "public city postgres",
"part-key": "city_id"
},
{
"oid": 317834,
"schema": "public",
"name": "film_category",
"reltuples": 1000,
"bytes": 73728,
"bytes-pretty": "72 kB",
"exclude-data": false,
"restore-list-name": "public film_category postgres",
"part-key": ""
},
{
"oid": 317798,
"schema": "public",
"name": "actor",
"reltuples": 200,
"bytes": 49152,
"bytes-pretty": "48 kB",
"exclude-data": false,
"restore-list-name": "public actor postgres",
"part-key": "actor_id"
},
{
"oid": 317924,
"schema": "public",
"name": "payment_p2020_05",
"reltuples": 182,
"bytes": 40960,
"bytes-pretty": "40 kB",
"exclude-data": false,
"restore-list-name": "public payment_p2020_05 postgres",
"part-key": ""
},
{
"oid": 317808,
"schema": "public",
"name": "category",
"reltuples": 0,
"bytes": 16384,
"bytes-pretty": "16 kB",
"exclude-data": false,
"restore-list-name": "public category postgres",
"part-key": "category_id"
},
{
"oid": 317865,
"schema": "public",
"name": "country",
"reltuples": 109,
"bytes": 16384,
"bytes-pretty": "16 kB",
"exclude-data": false,
"restore-list-name": "public country postgres",
"part-key": "country_id"
},
{
"oid": 317946,
"schema": "public",
"name": "staff",
"reltuples": 0,
"bytes": 16384,
"bytes-pretty": "16 kB",
"exclude-data": false,
"restore-list-name": "public staff postgres",
"part-key": "staff_id"
},
{
"oid": 378280,
"schema": "pgcopydb",
"name": "sentinel",
"reltuples": 1,
"bytes": 8192,
"bytes-pretty": "8192 bytes",
"exclude-data": false,
"restore-list-name": "pgcopydb sentinel dim",
"part-key": ""
},
{
"oid": 317892,
"schema": "public",
```
---
<pink>okay bestie</p> — take a sec and just *look* at this. 💖 That's the JSON flavour of the table list, and it's giving <purple>full receipts</purple> on every single table.
So what are you actually looking at here:
- the top-level <cyan>`"setup"`</cyan> object echoes back how the command was configured: the <cyan>`"snapshot"`</cyan> it used, the <cyan>`"source_pguri"`</cyan> and <cyan>`"target_pguri"`</cyan>, the <cyan>`"table-jobs"`</cyan> and <cyan>`"index-jobs"`</cyan> counts, and the <cyan>`"split-tables-larger-than"`</cyan> threshold. It's literally a lil' vibe check of your own flags. 🫶
- then <cyan>`"tables"`</cyan> is an array, one object per table, and each one carries its <cyan>`"oid"`</cyan>, <cyan>`"schema"`</cyan>, <cyan>`"name"`</cyan>, <cyan>`"reltuples"`</cyan>, raw <cyan>`"bytes"`</cyan>, the human-friendly <cyan>`"bytes-pretty"`</cyan>, an <cyan>`"exclude-data"`</cyan> flag, the <cyan>`"restore-list-name"`</cyan>, and the <cyan>`"part-key"`</cyan> ✨
- when a table got split, it also gets a <cyan>`"parts"`</cyan> array — and every part reports its <cyan>`"number"`</cyan>, the <cyan>`"total"`</cyan> number of parts, the <cyan>`"min"`</cyan> and <cyan>`"max"`</cyan> of the range, and the <cyan>`"count"`</cyan> of rows in it. Look at `rental` doing the absolute most with `7` parts, highkey iconic. 👑
> <yellow>reminder</yellow>: a `"part-key"` of `""` means no part key, so no `"parts"` array for that one — don't go looking for it, it's simply not there, and that's correct, no cap. 💅
<pink>`payment_p2020_04`</pink>, <pink>`payment_p2020_03`</pink>, <pink>`film_actor`</pink>, <pink>`payment_p2020_02`</pink>, <pink>`payment_p2020_01`</pink>, <pink>`payment_p2020_05`</pink> and <pink>`film_category`</pink> are all sitting there with `"part-key": ""` by the way, so they're just vibing as whole tables. 🦋 And yes, <purple>`pgcopydb`</purple> schemas show up in here too — hi <pink>`sentinel`</pink> with your `"reltuples": 1`, we see you and we love you. 🌸
It literally just *keeps going*, bestie — there's more table objects still coming. 🎀
<pink>okayyy</pink> we're still vibing inside the `json` output here, and the `tables` array is basically doing its outro 🎀 — just a couple more entries and then it hands the mic to `indexes`.
```json
"name": "language",
"reltuples": 0,
"bytes": 8192,
"bytes-pretty": "8192 bytes",
"exclude-data": false,
"restore-list-name": "public language postgres",
"part-key": "language_id"
},
{
"oid": 317928,
"schema": "public",
"name": "payment_p2020_06",
"reltuples": 0,
"bytes": 8192,
"bytes-pretty": "8192 bytes",
"exclude-data": false,
"restore-list-name": "public payment_p2020_06 postgres",
"part-key": ""
},
{
"oid": 317957,
"schema": "public",
"name": "store",
"reltuples": 0,
"bytes": 8192,
"bytes-pretty": "8192 bytes",
"exclude-data": false,
"restore-list-name": "public store postgres",
"part-key": "store_id"
}
],
```
<lavender>not me zooming in on the details</lavender> 👀 — see how every table entry rolls with `oid`, `schema`, `name`, `reltuples`, `bytes`, `bytes-pretty`, `exclude-data`, `restore-list-name` and `part-key`? And like, `part-key` isn't always filled in bestie: `language` says `"language_id"`, `store` says `"store_id"`, but `payment_p2020_06` is just sitting there with an empty string `""`. <cyan>it's giving</cyan> *some tables have partition keys, some simply do not*, and the field still shows up anyway. Consistency queen. 👑
Also <peach>`"exclude-data": false`</peach> on all three, and `"reltuples": 0` — zero rows, because this is a fresh-ish catalog situation, and the pretty version of the size is right there next to the raw number ✨.
---
And now, the moment we've been waiting for, <rainbow>the `indexes` array</rainbow>. 🌈
```json
"indexes": [
{
"oid": 378283,
"schema": "pgcopydb",
"name": "sentinel_expr_idx",
"isPrimary": false,
"isUnique": true,
"columns": "",
"sql": "CREATE UNIQUE INDEX sentinel_expr_idx ON pgcopydb.sentinel USING btree ((1))",
"restore-list-name": "pgcopydb sentinel_expr_idx dim",
"table": {
"oid": 378280,
"schema": "pgcopydb",
"name": "sentinel"
}
},
{
"oid": 318001,
"schema": "public",
"name": "idx_actor_last_name",
"isPrimary": false,
"isUnique": false,
"columns": "last_name",
"sql": "CREATE INDEX idx_actor_last_name ON public.actor USING btree (last_name)",
"restore-list-name": "public idx_actor_last_name postgres",
"table": {
"oid": 317798,
"schema": "public",
"name": "actor"
}
},
{
"oid": 317972,
"schema": "public",
"name": "actor_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "actor_id",
"sql": "CREATE UNIQUE INDEX actor_pkey ON public.actor USING btree (actor_id)",
"restore-list-name": "",
"table": {
"oid": 317798,
"schema": "public",
"name": "actor"
},
"constraint": {
"oid": 317973,
"name": "actor_pkey",
"sql": "PRIMARY KEY (actor_id)"
}
},
```
<purple>okay so</purple> here's the pattern and it is *so* satisfying once you see it 💅 — each index object carries `oid`, `schema`, `name`, `isPrimary`, `isUnique`, `columns`, the full `sql` that would recreate it, a `restore-list-name`, and a nested `table` object with that table's `oid`, `schema` and `name`.
Things worth clocking 🔍:
- <hotpink>`sentinel_expr_idx`</hotpink> is `"isPrimary": false` and `"isUnique": true`, and its `columns` is an empty string `""` — because it's an **expression** index: `CREATE UNIQUE INDEX sentinel_expr_idx ON pgcopydb.sentinel USING btree ((1))`. No plain column name to list, so the field just goes blank. Iconic behaviour honestly.
- <mint>`idx_actor_last_name`</mint> is a plain old `"isPrimary": false, "isUnique": false` index on `"columns": "last_name"`.
- <purple>`actor_pkey`</purple> is `"isPrimary": true` **and** `"isUnique": true`, with `"columns": "actor_id"` — and notice the two extra things it gets that the others don't: its `restore-list-name` is the empty string `""`, and it has a whole extra nested `constraint` object (`oid` `317973`, `name` `actor_pkey`, `sql` `PRIMARY KEY (actor_id)`).
- Every one of these points back at its parent with a `table` object — `<cyan>378280 pgcopydb sentinel</cyan>`, `<cyan>317798 public actor</cyan>`, `<cyan>317798 public actor</cyan>` — so you can always trace an index home. 🏠
---
And the parade keeps marching, because of course it does 🚶♀️✨:
```json
{
"oid": 317974,
"schema": "public",
"name": "address_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "address_id",
"sql": "CREATE UNIQUE INDEX address_pkey ON public.address USING btree (address_id)",
"restore-list-name": "",
"table": {
"oid": 317845,
"schema": "public",
"name": "address"
},
"constraint": {
"oid": 317975,
"name": "address_pkey",
"sql": "PRIMARY KEY (address_id)"
}
},
{
"oid": 318003,
"schema": "public",
"name": "idx_fk_city_id",
"isPrimary": false,
"isUnique": false,
"columns": "city_id",
"sql": "CREATE INDEX idx_fk_city_id ON public.address USING btree (city_id)",
"restore-list-name": "public idx_fk_city_id postgres",
"table": {
"oid": 317845,
"schema": "public",
"name": "address"
}
},
{
"oid": 317976,
"schema": "public",
"name": "category_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "category_id",
"sql": "CREATE UNIQUE INDEX category_pkey ON public.category USING btree (category_id)",
"restore-list-name": "",
"table": {
"oid": 317808,
"schema": "public",
"name": "category"
},
"constraint": {
"oid": 317977,
"name": "category_pkey",
"sql": "PRIMARY KEY (category_id)"
}
},
{
"oid": 317978,
"schema": "public",
"name": "city_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "city_id",
"sql": "CREATE UNIQUE INDEX city_pkey ON public.city USING btree (city_id)",
"restore-list-name": "",
"table": {
"oid": 317855,
"schema": "public",
"name": "city"
},
"constraint": {
"oid": 317979,
"name": "city_pkey",
"sql": "PRIMARY KEY (city_id)"
}
},
{
"oid": 318004,
"schema": "public",
"name": "idx_fk_country_id",
"isPrimary": false,
"isUnique": false,
"columns": "country_id",
"sql": "CREATE INDEX idx_fk_country_id ON public.city USING btree (country_id)",
"restore-list-name": "public idx_fk_country_id postgres",
"table": {
"oid": 317855,
"schema": "public",
"name": "city"
}
},
{
"oid": 317980,
"schema": "public",
"name": "country_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "country_id",
"sql": "CREATE UNIQUE INDEX country_pkey ON public.country USING btree (country_id)",
"restore-list-name": "",
"table": {
"oid": 317865,
"schema": "public",
"name": "country"
},
"constraint": {
"oid": 317981,
"name": "country_pkey",
"sql": "PRIMARY KEY (country_id)"
}
},
```
<yellow>the way that</yellow> the primary keys are lowkey all twins 🫶 — `address_pkey`, `category_pkey`, `city_pkey`, `country_pkey`, every single one going `"isPrimary": true`, `"isUnique": true`, `"restore-list-name": ""` and dragging along its own little `constraint` object. Whereas the foreign-key indexes (`idx_fk_city_id`, `idx_fk_country_id`) are plain `false`/`false` and *do* get a `restore-list-name` filled in. <pink>vibe check passed</pink> ✅
Note that `address` appears twice in a row here — once via its `address_pkey`, once via `idx_fk_city_id` — and both nest `"table"` right back to `"oid": 317845, "schema": "public", "name": "address"`. Same with `city` and `317855`. So if you're grepping for *everything touching one table*, you flex on it by matching the nested `table.oid`, not by hoping the entries are neighbours. 😌
---
And last stretch of this chunk, bestie — `<purple>customer</purple>` is about to show up *four separate times* 💀:
```json
{
"oid": 318024,
"schema": "public",
"name": "idx_last_name",
"isPrimary": false,
"isUnique": false,
"columns": "last_name",
"sql": "CREATE INDEX idx_last_name ON public.customer USING btree (last_name)",
"restore-list-name": "public idx_last_name postgres",
"table": {
"oid": 317784,
"schema": "public",
"name": "customer"
}
},
{
"oid": 318002,
"schema": "public",
"name": "idx_fk_address_id",
"isPrimary": false,
"isUnique": false,
"columns": "address_id",
"sql": "CREATE INDEX idx_fk_address_id ON public.customer USING btree (address_id)",
"restore-list-name": "public idx_fk_address_id postgres",
"table": {
"oid": 317784,
"schema": "public",
"name": "customer"
}
},
{
"oid": 317982,
"schema": "public",
"name": "customer_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "customer_id",
"sql": "CREATE UNIQUE INDEX customer_pkey ON public.customer USING btree (customer_id)",
"restore-list-name": "",
"table": {
"oid": 317784,
"schema": "public",
"name": "customer"
},
"constraint": {
"oid": 317983,
"name": "customer_pkey",
"sql": "PRIMARY KEY (customer_id)"
}
},
{
"oid": 318023,
"schema": "public",
"name": "idx_fk_store_id",
"isPrimary": false,
"isUnique": false,
"columns": "store_id",
"sql": "CREATE INDEX idx_fk_store_id ON public.customer USING btree (store_id)",
"restore-list-name": "public idx_fk_store_id postgres",
"table": {
"oid": 317784,
"schema": "public",
"name": "customer"
}
},
{
"oid": 318009,
"schema": "public",
"name": "idx_fk_original_language_id",
"isPrimary": false,
"isUnique": false,
"columns": "original_language_id",
"sql": "CREATE INDEX idx_fk_original_language_id ON public.film USING btree (original_language_id)",
"restore-list-name": "public idx_fk_original_language_id postgres",
"table": {
"oid": 317818,
"schema": "public",
"name": "film"
}
},
{
"oid": 318026,
"schema": "public",
"name": "idx_title",
"isPrimary": false,
```
<lavender>ngl</lavender> `customer` (`oid` `317784`) is really out here collecting indexes like stickers 🍓 — `idx_last_name`, `idx_fk_address_id`, `customer_pkey`, `idx_fk_store_id` — while `film` (`oid` `317818`) sneaks in with `idx_fk_original_language_id`, and then `idx_title` (`oid` `318026`, `"isPrimary": false`) starts gearing up right as this part cuts off. <hotpink>cliffhanger energy</hotpink> 😭
`customer_pkey` is the usual primary-key situation again: `"isPrimary": true`, `"isUnique": true`, `"columns": "customer_id"`, `"restore-list-name": ""`, plus that nested `constraint` holding `oid` `317983`, `name` `customer_pkey` and `sql` `PRIMARY KEY (customer_id)`. Meanwhile the non-primary ones are all `false`/`false`, with their `columns` set to the actual column *and* a filled-in `restore-list-name` like `"public idx_fk_store_id postgres"`. 👑
And `idx_title` hasn't even told us its full story yet — it's just sitting there with `"isPrimary": false` and a comma, waiting. <rainbow>to be continued…</rainbow> 🌈💜
...and the `film` table is *still* serving looks, bestie 💅 the index dump keeps going — we're picking up mid-object, right where we left off:
```
"isUnique": false,
"columns": "title",
"sql": "CREATE INDEX idx_title ON public.film USING btree (title)",
"restore-list-name": "public idx_title postgres",
"table": {
"oid": 317818,
"schema": "public",
"name": "film"
}
},
{
"oid": 318000,
"schema": "public",
"name": "film_fulltext_idx",
"isPrimary": false,
"isUnique": false,
"columns": "fulltext",
"sql": "CREATE INDEX film_fulltext_idx ON public.film USING gist (fulltext)",
"restore-list-name": "public film_fulltext_idx postgres",
"table": {
"oid": 317818,
"schema": "public",
"name": "film"
}
},
{
"oid": 317988,
"schema": "public",
"name": "film_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "film_id",
"sql": "CREATE UNIQUE INDEX film_pkey ON public.film USING btree (film_id)",
"restore-list-name": "",
"table": {
"oid": 317818,
"schema": "public",
"name": "film"
},
"constraint": {
"oid": 317989,
"name": "film_pkey",
"sql": "PRIMARY KEY (film_id)"
}
},
```
okay so <pink>spot the pattern</pink> queen 👑 — the primary-key ones (`film_pkey` and its little friends) come rocking an empty `"restore-list-name": ""` **plus** their very own `"constraint"` block (with its own `"oid"`, `"name"` and `"sql"`), while the plain indexes carry a restore-list-name like `"public idx_title postgres"` and no constraint block at all. iykyk 💖 anyway, carry on, the keys keep coming:
```
{
"oid": 318008,
"schema": "public",
"name": "idx_fk_language_id",
"isPrimary": false,
"isUnique": false,
"columns": "language_id",
"sql": "CREATE INDEX idx_fk_language_id ON public.film USING btree (language_id)",
"restore-list-name": "public idx_fk_language_id postgres",
"table": {
"oid": 317818,
"schema": "public",
"name": "film"
}
},
{
"oid": 317984,
"schema": "public",
"name": "film_actor_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "actor_id,film_id",
"sql": "CREATE UNIQUE INDEX film_actor_pkey ON public.film_actor USING btree (actor_id, film_id)",
"restore-list-name": "",
"table": {
"oid": 317830,
"schema": "public",
"name": "film_actor"
},
"constraint": {
"oid": 317985,
"name": "film_actor_pkey",
"sql": "PRIMARY KEY (actor_id, film_id)"
}
},
{
"oid": 318006,
"schema": "public",
"name": "idx_fk_film_id",
"isPrimary": false,
"isUnique": false,
"columns": "film_id",
"sql": "CREATE INDEX idx_fk_film_id ON public.film_actor USING btree (film_id)",
"restore-list-name": "public idx_fk_film_id postgres",
"table": {
"oid": 317830,
"schema": "public",
"name": "film_actor"
}
},
```
not the `film_actor` table having a two-column primary key (`"columns": "actor_id,film_id"`) 🌈 and the join tables are serving exactly as expected. more incoming:
```
{
"oid": 317986,
"schema": "public",
"name": "film_category_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "film_id,category_id",
"sql": "CREATE UNIQUE INDEX film_category_pkey ON public.film_category USING btree (film_id, category_id)",
"restore-list-name": "",
"table": {
"oid": 317834,
"schema": "public",
"name": "film_category"
},
"constraint": {
"oid": 317987,
"name": "film_category_pkey",
"sql": "PRIMARY KEY (film_id, category_id)"
}
},
{
"oid": 318025,
"schema": "public",
"name": "idx_store_id_film_id",
"isPrimary": false,
"isUnique": false,
"columns": "film_id,store_id",
"sql": "CREATE INDEX idx_store_id_film_id ON public.inventory USING btree (store_id, film_id)",
"restore-list-name": "public idx_store_id_film_id postgres",
"table": {
"oid": 317885,
"schema": "public",
"name": "inventory"
}
},
{
"oid": 317990,
"schema": "public",
"name": "inventory_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "inventory_id",
"sql": "CREATE UNIQUE INDEX inventory_pkey ON public.inventory USING btree (inventory_id)",
"restore-list-name": "",
"table": {
"oid": 317885,
"schema": "public",
"name": "inventory"
},
"constraint": {
"oid": 317991,
"name": "inventory_pkey",
"sql": "PRIMARY KEY (inventory_id)"
}
},
{
"oid": 317992,
"schema": "public",
"name": "language_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "language_id",
"sql": "CREATE UNIQUE INDEX language_pkey ON public.language USING btree (language_id)",
"restore-list-name": "",
"table": {
"oid": 317892,
"schema": "public",
"name": "language"
},
"constraint": {
"oid": 317993,
"name": "language_pkey",
"sql": "PRIMARY KEY (language_id)"
}
},
```
<lavender>obsessed</lavender> with how clean this is ✨ and now for the payment partitions, because of *course* payments said "let me be extra" and got like four indexes per partition 🙈 the dramatics:
```
{
"oid": 318010,
"schema": "public",
"name": "idx_fk_payment_p2020_01_customer_id",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_01_customer_id ON public.payment_p2020_01 USING btree (customer_id)",
"restore-list-name": "public idx_fk_payment_p2020_01_customer_id postgres",
"table": {
"oid": 317908,
"schema": "public",
"name": "payment_p2020_01"
}
},
{
"oid": 318029,
"schema": "public",
"name": "payment_p2020_01_customer_id_idx",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX payment_p2020_01_customer_id_idx ON public.payment_p2020_01 USING btree (customer_id)",
"restore-list-name": "public payment_p2020_01_customer_id_idx postgres",
"table": {
"oid": 317908,
"schema": "public",
"name": "payment_p2020_01"
}
},
{
"oid": 318012,
"schema": "public",
"name": "idx_fk_payment_p2020_01_staff_id",
"isPrimary": false,
"isUnique": false,
"columns": "staff_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_01_staff_id ON public.payment_p2020_01 USING btree (staff_id)",
"restore-list-name": "public idx_fk_payment_p2020_01_staff_id postgres",
"table": {
"oid": 317908,
"schema": "public",
"name": "payment_p2020_01"
}
},
{
"oid": 318013,
"schema": "public",
"name": "idx_fk_payment_p2020_02_customer_id",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_02_customer_id ON public.payment_p2020_02 USING btree (customer_id)",
"restore-list-name": "public idx_fk_payment_p2020_02_customer_id postgres",
"table": {
"oid": 317912,
"schema": "public",
"name": "payment_p2020_02"
}
},
{
"oid": 318014,
"schema": "public",
"name": "idx_fk_payment_p2020_02_staff_id",
"isPrimary": false,
"isUnique": false,
"columns": "staff_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_02_staff_id ON public.payment_p2020_02 USING btree (staff_id)",
"restore-list-name": "public idx_fk_payment_p2020_02_staff_id postgres",
"table": {
"oid": 317912,
"schema": "public",
"name": "payment_p2020_02"
}
},
{
"oid": 318030,
"schema": "public",
"name": "payment_p2020_02_customer_id_idx",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX payment_p2020_02_customer_id_idx ON public.payment_p2020_02 USING btree (customer_id)",
"restore-list-name": "public payment_p2020_02_customer_id_idx postgres",
"table": {
"oid": 317912,
"schema": "public",
"name": "payment_p2020_02"
}
},
```
see how `payment_p2020_01` rocks both `idx_fk_payment_p2020_01_customer_id` **and** `payment_p2020_01_customer_id_idx` on the same `customer_id` column?? 😭 two indexes, same column, no shame — real main character energy fr fr. and bestie we are *not* done, the dump literally ends on a dangling comma so...
...and the <purple>index parade</purple> just *keeps going*, bestie, so let's keep scrolling through the rest of the `"indexes"` array. Same shape every single time 💖 — `oid`, `schema`, `name`, `isPrimary`, `isUnique`, `columns`, `sql`, `restore-list-name`, and a little `table` object (or a `constraint` object when it's a primary key, iykyk) — and yes, the `sql` values are the literal statements, so I'm giving them to you exactly as they came:
```
"oid": 318016,
"schema": "public",
"name": "idx_fk_payment_p2020_03_staff_id",
"isPrimary": false,
"isUnique": false,
"columns": "staff_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_03_staff_id ON public.payment_p2020_03 USING btree (staff_id)",
"restore-list-name": "public idx_fk_payment_p2020_03_staff_id postgres",
"table": {
"oid": 317916,
"schema": "public",
"name": "payment_p2020_03"
}
},
{
"oid": 318031,
"schema": "public",
"name": "payment_p2020_03_customer_id_idx",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX payment_p2020_03_customer_id_idx ON public.payment_p2020_03 USING btree (customer_id)",
"restore-list-name": "public payment_p2020_03_customer_id_idx postgres",
"table": {
"oid": 317916,
"schema": "public",
"name": "payment_p2020_03"
}
},
{
"oid": 318015,
"schema": "public",
"name": "idx_fk_payment_p2020_03_customer_id",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_03_customer_id ON public.payment_p2020_03 USING btree (customer_id)",
"restore-list-name": "public idx_fk_payment_p2020_03_customer_id postgres",
"table": {
"oid": 317916,
"schema": "public",
"name": "payment_p2020_03"
}
},
{
"oid": 318032,
"schema": "public",
"name": "payment_p2020_04_customer_id_idx",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX payment_p2020_04_customer_id_idx ON public.payment_p2020_04 USING btree (customer_id)",
"restore-list-name": "public payment_p2020_04_customer_id_idx postgres",
"table": {
"oid": 317920,
"schema": "public",
"name": "payment_p2020_04"
}
},
{
"oid": 318018,
"schema": "public",
"name": "idx_fk_payment_p2020_04_staff_id",
"isPrimary": false,
"isUnique": false,
"columns": "staff_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_04_staff_id ON public.payment_p2020_04 USING btree (staff_id)",
"restore-list-name": "public idx_fk_payment_p2020_04_staff_id postgres",
"table": {
"oid": 317920,
"schema": "public",
"name": "payment_p2020_04"
}
},
{
"oid": 318017,
"schema": "public",
"name": "idx_fk_payment_p2020_04_customer_id",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_04_customer_id ON public.payment_p2020_04 USING btree (customer_id)",
"restore-list-name": "public idx_fk_payment_p2020_04_customer_id postgres",
"table": {
"oid": 317920,
"schema": "public",
"name": "payment_p2020_04"
}
},
{
"oid": 318019,
"schema": "public",
"name": "idx_fk_payment_p2020_05_customer_id",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_05_customer_id ON public.payment_p2020_05 USING btree (customer_id)",
"restore-list-name": "public idx_fk_payment_p2020_05_customer_id postgres",
"table": {
"oid": 317924,
"schema": "public",
"name": "payment_p2020_05"
}
},
{
"oid": 318020,
"schema": "public",
"name": "idx_fk_payment_p2020_05_staff_id",
"isPrimary": false,
"isUnique": false,
"columns": "staff_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_05_staff_id ON public.payment_p2020_05 USING btree (staff_id)",
"restore-list-name": "public idx_fk_payment_p2020_05_staff_id postgres",
"table": {
"oid": 317924,
"schema": "public",
"name": "payment_p2020_05"
}
},
{
"oid": 318033,
"schema": "public",
"name": "payment_p2020_05_customer_id_idx",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX payment_p2020_05_customer_id_idx ON public.payment_p2020_05 USING btree (customer_id)",
"restore-list-name": "public payment_p2020_05_customer_id_idx postgres",
"table": {
"oid": 317924,
"schema": "public",
"name": "payment_p2020_05"
}
},
{
"oid": 318022,
"schema": "public",
"name": "idx_fk_payment_p2020_06_staff_id",
"isPrimary": false,
"isUnique": false,
"columns": "staff_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_06_staff_id ON public.payment_p2020_06 USING btree (staff_id)",
"restore-list-name": "public idx_fk_payment_p2020_06_staff_id postgres",
"table": {
"oid": 317928,
"schema": "public",
"name": "payment_p2020_06"
}
},
{
"oid": 318034,
"schema": "public",
"name": "payment_p2020_06_customer_id_idx",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX payment_p2020_06_customer_id_idx ON public.payment_p2020_06 USING btree (customer_id)",
"restore-list-name": "public payment_p2020_06_customer_id_idx postgres",
"table": {
"oid": 317928,
"schema": "public",
"name": "payment_p2020_06"
}
},
{
"oid": 318021,
"schema": "public",
"name": "idx_fk_payment_p2020_06_customer_id",
"isPrimary": false,
"isUnique": false,
"columns": "customer_id",
"sql": "CREATE INDEX idx_fk_payment_p2020_06_customer_id ON public.payment_p2020_06 USING btree (customer_id)",
"restore-list-name": "public idx_fk_payment_p2020_06_customer_id postgres",
"table": {
"oid": 317928,
"schema": "public",
"name": "payment_p2020_06"
}
},
{
"oid": 318028,
"schema": "public",
"name": "idx_unq_rental_rental_date_inventory_id_customer_id",
"isPrimary": false,
"isUnique": true,
"columns": "rental_date,inventory_id,customer_id",
"sql": "CREATE UNIQUE INDEX idx_unq_rental_rental_date_inventory_id_customer_id ON public.rental USING btree (rental_date, inventory_id, customer_id)",
"restore-list-name": "public idx_unq_rental_rental_date_inventory_id_customer_id postgres",
"table": {
"oid": 317934,
"schema": "public",
"name": "rental"
}
},
{
"oid": 317994,
"schema": "public",
"name": "rental_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "rental_id",
"sql": "CREATE UNIQUE INDEX rental_pkey ON public.rental USING btree (rental_id)",
"restore-list-name": "",
"table": {
"oid": 317934,
"schema": "public",
"name": "rental"
},
"constraint": {
"oid": 317995,
"name": "rental_pkey",
"sql": "PRIMARY KEY (rental_id)"
}
},
{
"oid": 318007,
"schema": "public",
"name": "idx_fk_inventory_id",
"isPrimary": false,
"isUnique": false,
"columns": "inventory_id",
"sql": "CREATE INDEX idx_fk_inventory_id ON public.rental USING btree (inventory_id)",
"restore-list-name": "public idx_fk_inventory_id postgres",
"table": {
"oid": 317934,
"schema": "public",
"name": "rental"
}
},
{
"oid": 317996,
"schema": "public",
"name": "staff_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "staff_id",
"sql": "CREATE UNIQUE INDEX staff_pkey ON public.staff USING btree (staff_id)",
"restore-list-name": "",
"table": {
"oid": 317946,
"schema": "public",
"name": "staff"
},
"constraint": {
"oid": 317997,
"name": "staff_pkey",
```
---
okay so let's <cyan>vibe check</cyan> what just flew past, because a few things are <hotpink>highkey</hotpink> worth noticing 🫶:
- Every one of these entries carries its own `oid`, `schema`, `name`, `isPrimary`, `isUnique`, `columns`, `sql` and `restore-list-name`, plus the `table` object telling you which `oid`/`schema`/`name` it belongs to. Consistency? <rainbow>Iconic.</rainbow> 💅
- The composite one, `idx_unq_rental_rental_date_inventory_id_customer_id`, is the only one in this batch with `"isUnique": true` and `"isPrimary": false`, and its `columns` is the comma-joined `rental_date,inventory_id,customer_id`. Slay.
- The two primary keys — `rental_pkey` and `staff_pkey` — are the ones with `"isPrimary": true`, `"isUnique": true`, and <yellow>empty string</yellow> `"restore-list-name": ""`. They also get that extra nested `constraint` object with its own `oid`, `name` and `sql` (`"PRIMARY KEY (rental_id)"` for the first, and the second is still mid-object…) ✨
- Everything else has a real `restore-list-name` like `"public idx_fk_payment_p2020_03_staff_id postgres"` — non-empty, all lowercase and space-separated. Cute.
> heads up bestie: the last object in this chunk is literally cut off mid-JSON (`"name": "staff_pkey",` with no closing brace yet) because we're deep in the middle of the array, not because anything's broken. Keep scrolling with me 💖
"sql": "PRIMARY KEY (staff_id)"
}
},
{
"oid": 318027,
"schema": "public",
"name": "idx_unq_manager_staff_id",
"isPrimary": false,
"isUnique": true,
"columns": "manager_staff_id",
"sql": "CREATE UNIQUE INDEX idx_unq_manager_staff_id ON public.store USING btree (manager_staff_id)",
"restore-list-name": "public idx_unq_manager_staff_id postgres",
"table": {
"oid": 317957,
"schema": "public",
"name": "store"
}
},
{
"oid": 317998,
"schema": "public",
"name": "store_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "store_id",
"sql": "CREATE UNIQUE INDEX store_pkey ON public.store USING btree (store_id)",
"restore-list-name": "",
"table": {
"oid": 317957,
"schema": "public",
"name": "store"
},
"constraint": {
"oid": 317999,
"name": "store_pkey",
"sql": "PRIMARY KEY (store_id)"
}
}
],
"sequences": [
{
"oid": 317796,
"schema": "public",
"name": "actor_actor_id_seq",
"last-value": 200,
"is-called": true,
"restore-list-name": "public actor_actor_id_seq postgres"
},
{
"oid": 317843,
"schema": "public",
"name": "address_address_id_seq",
"last-value": 605,
"is-called": true,
"restore-list-name": "public address_address_id_seq postgres"
},
{
"oid": 317806,
"schema": "public",
"name": "category_category_id_seq",
"last-value": 16,
"is-called": true,
"restore-list-name": "public category_category_id_seq postgres"
},
{
"oid": 317853,
"schema": "public",
"name": "city_city_id_seq",
"last-value": 600,
"is-called": true,
"restore-list-name": "public city_city_id_seq postgres"
},
{
"oid": 317863,
"schema": "public",
"name": "country_country_id_seq",
"last-value": 109,
"is-called": true,
"restore-list-name": "public country_country_id_seq postgres"
},
{
"oid": 317782,
"schema": "public",
"name": "customer_customer_id_seq",
"last-value": 599,
"is-called": true,
"restore-list-name": "public customer_customer_id_seq postgres"
},
{
"oid": 317816,
"schema": "public",
"name": "film_film_id_seq",
"last-value": 1000,
"is-called": true,
"restore-list-name": "public film_film_id_seq postgres"
},
{
"oid": 317883,
"schema": "public",
"name": "inventory_inventory_id_seq",
"last-value": 4581,
"is-called": true,
"restore-list-name": "public inventory_inventory_id_seq postgres"
},
{
"oid": 317890,
"schema": "public",
"name": "language_language_id_seq",
"last-value": 6,
"is-called": true,
"restore-list-name": "public language_language_id_seq postgres"
},
{
"oid": 317902,
"schema": "public",
"name": "payment_payment_id_seq",
"last-value": 32099,
"is-called": true,
"restore-list-name": "public payment_payment_id_seq postgres"
},
{
"oid": 317932,
"schema": "public",
"name": "rental_rental_id_seq",
"last-value": 16050,
"is-called": true,
"restore-list-name": "public rental_rental_id_seq postgres"
},
{
"oid": 317944,
"schema": "public",
"name": "staff_staff_id_seq",
"last-value": 2,
"is-called": true,
"restore-list-name": "public staff_staff_id_seq postgres"
},
{
"oid": 317955,
"schema": "public",
"name": "store_store_id_seq",
"last-value": 2,
"is-called": true,
"restore-list-name": "public store_store_id_seq postgres"
}
]
}
```
---
<rainbow>okay and now for the part everyone actually opens the terminal for</rainbow> 💅 <pink>the progress vibe check.</pink> Say you just wanna see where things are at *right now* — no log spam, no drama, just the numbers:
**Listing current progress (log lines removed):**
```
$ pgcopydb list progress 2>/dev/null
| Total Count | In Progress | Done
-------------+--------------+--------------+-------------
Tables | 21 | 4 | 7
Indexes | 48 | 14 | 7
```
Like. <hotpink>LOOK AT IT.</hotpink> 🎀 Three little columns, one for <italic>Total Count</italic>, one for <italic>In Progress</italic>, one for <italic>Done</italic>, and your Tables and your Indexes rowing along. <mint>chef's kiss ✨</mint> Notice how we piped <kbd>2</kbd> into <kbd>/dev/null</kbd> up there? That's the "log lines removed" part bestie — stderr goes bye bye, only the pretty table stays. 🦋
And because we are <purple>nothing if not flexible</purple>, you can get that exact same progress report dressed up as JSON instead. 💜
**Listing current progress, in JSON:**
```
$ pgcopydb list progress --json 2>/dev/null
{
"table-jobs": 4,
"index-jobs": 4,
"tables": {
"total": 21,
"done": 9,
"in-progress": [
{
"oid": 317908,
"schema": "public",
"name": "payment_p2020_01",
"reltuples": 1157,
"bytes": 98304,
"bytes-pretty": "96 kB",
"exclude-data": false,
"restore-list-name": "public payment_p2020_01 postgres",
"part-key": "",
"process": {
"pid": 75159,
"start-time-epoch": 1662476249,
"start-time-string": "2022-09-06 16:57:29 CEST",
"command": "COPY \"public\".\"payment_p2020_01\""
}
},
{
"oid": 317855,
"schema": "public",
"name": "city",
"reltuples": 600,
"bytes": 73728,
"bytes-pretty": "72 kB",
"exclude-data": false,
"restore-list-name": "public city postgres",
"part-key": "city_id",
"process": {
"pid": 75157,
"start-time-epoch": 1662476249,
"start-time-string": "2022-09-06 16:57:29 CEST",
"command": "COPY \"public\".\"city\""
}
}
]
},
"indexes": {
"total": 48,
"done": 39,
"in-progress": [
{
"oid": 378283,
"schema": "pgcopydb",
"name": "sentinel_expr_idx",
"isPrimary": false,
"isUnique": true,
"columns": "",
"sql": "CREATE UNIQUE INDEX sentinel_expr_idx ON pgcopydb.sentinel USING btree ((1))",
"restore-list-name": "pgcopydb sentinel_expr_idx dim",
"table": {
"oid": 378280,
"schema": "pgcopydb",
"name": "sentinel"
},
"process": {
"pid": 74372,
"start-time-epoch": 1662476080,
"start-time-string": "2022-09-06 16:54:40 CEST"
}
},
{
"oid": 317980,
"schema": "public",
"name": "country_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "country_id",
"sql": "CREATE UNIQUE INDEX country_pkey ON public.country USING btree (country_id)",
"restore-list-name": "public country_pkey postgres",
"table": {
"oid": 317865,
"schema": "public",
"name": "country"
},
"constraint": {
"oid": 317981,
"name": "country_pkey",
"sql": "PRIMARY KEY (country_id)",
"restore-list-name": ""
},
"process": {
"pid": 74358,
"start-time-epoch": 1662476080,
"start-time-string": "2022-09-06 16:54:40 CEST"
}
},
{
"oid": 317996,
"schema": "public",
"name": "staff_pkey",
"isPrimary": true,
"isUnique": true,
"columns": "staff_id",
"sql": "CREATE UNIQUE INDEX staff_pkey ON public.staff USING btree (staff_id)",
"restore-list-name": "public staff_pkey postgres",
"table": {
"oid": 317946,
"schema": "public",
"name": "staff"
},
"constraint": {
"oid": 317997,
"name": "staff_pkey",
"sql": "PRIMARY KEY (staff_id)",
"restore-list-name": ""
},
"process": {
"pid": 74368,
"start-time-epoch": 1662476080,
"start-time-string": "2022-09-06 16:54:40 CEST"
}
}
]
}
}
```
okay so that's a LOT of JSON and honestly? it's giving ✨full transparency✨. `"table-jobs"`, `"index-jobs"`, `"total"`, `"done"`, and then the `"in-progress"` list of every single thing currently being worked on, each one carrying its own `"oid"`, `"schema"`, `"name"`, `"process"` with `"pid"`, `"start-time-epoch"` and `"start-time-string"` 🫶. The table entries also bring `"reltuples"`, `"bytes"`, `"bytes-pretty"`, `"exclude-data"`, `"restore-list-name"`, `"part-key"` and a `"command"`. The index entries bring `"isPrimary"`, `"isUnique"`, `"columns"`, `"sql"`, `"restore-list-name"`, a nested `"table"`, sometimes a nested `"constraint"`, and their own `"process"`. bestie, scroll it, grep it, pipe it into `jq`, live your truth 💅
---
# 👑 AUTHOR 👑
**Dimitri Fontaine** — the icon, the moment, the whole entire reason this exists 💖
# 📜 COPYRIGHT 📜
**Copyright 2022-2024, Dimitri Fontaine**
---
and that's a wrap on the whole page, bestie 🎀 you made it to the very end and I'm so proud of you fr fr 🌸 this beauty is `pgcopydb` version `0.18`, dated `Jun 28, 2026`, and it is the one and only `PGCOPYDB LIST(1)` manual page 👑 go forth and list everything ✨💜
📦 where this page comes from
- ubuntu stonking: pgcopydb 0.18-1 · PostgreSQL
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms