pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump and restore without indexes
6+ messages / 3 participants
[nested] [flat]
* pg_dump and restore without indexes
@ 2024-06-04 17:42 Teja Jakkidi <teja.jakkidi05@gmail.com>
2024-06-04 17:56 ` Re: pg_dump and restore without indexes Erik Wienhold <ewie@ewie.name>
2024-06-04 18:15 ` pg_dump and restore without indexes Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
0 siblings, 2 replies; 6+ messages in thread
From: Teja Jakkidi @ 2024-06-04 17:42 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello Admins,
I am trying to look for an option that can be added in pg_dump command to ignore all the indexes when creating the schema dump with data.
Is there any such option that can be used in pg_dump? Or in pg_restore?
Please help with your inputs.
Thanks in advance,
J. Teja.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump and restore without indexes
2024-06-04 17:42 pg_dump and restore without indexes Teja Jakkidi <teja.jakkidi05@gmail.com>
@ 2024-06-04 17:56 ` Erik Wienhold <ewie@ewie.name>
2024-06-04 17:58 ` Re: pg_dump and restore without indexes Teja Jakkidi <teja.jakkidi05@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Erik Wienhold @ 2024-06-04 17:56 UTC (permalink / raw)
To: Teja Jakkidi <teja.jakkidi05@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
On 2024-06-04 19:42 +0200, Teja Jakkidi wrote:
> I am trying to look for an option that can be added in pg_dump command
> to ignore all the indexes when creating the schema dump with data. Is
> there any such option that can be used in pg_dump? Or in pg_restore?
You can get the table of contents with pg_restore --list and remove or
comment out the INDEX entries in that file. Then feed the TOC back into
pg_restore with --use-list. The implicit indexes for primary key and
unique constraints will still be created, though.
--
Erik
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump and restore without indexes
2024-06-04 17:42 pg_dump and restore without indexes Teja Jakkidi <teja.jakkidi05@gmail.com>
2024-06-04 17:56 ` Re: pg_dump and restore without indexes Erik Wienhold <ewie@ewie.name>
@ 2024-06-04 17:58 ` Teja Jakkidi <teja.jakkidi05@gmail.com>
2024-06-04 18:27 ` Re: pg_dump and restore without indexes Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 6+ messages in thread
From: Teja Jakkidi @ 2024-06-04 17:58 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Thank you, Erik.
Will try this option.
Also, is there a way we can remap schema or table during restore like how we have an option to remap in Oracle?
Thank,
J. Teja.
> On Jun 4, 2024, at 10:56 AM, Erik Wienhold <ewie@ewie.name> wrote:
>
> On 2024-06-04 19:42 +0200, Teja Jakkidi wrote:
>> I am trying to look for an option that can be added in pg_dump command
>> to ignore all the indexes when creating the schema dump with data. Is
>> there any such option that can be used in pg_dump? Or in pg_restore?
>
> You can get the table of contents with pg_restore --list and remove or
> comment out the INDEX entries in that file. Then feed the TOC back into
> pg_restore with --use-list. The implicit indexes for primary key and
> unique constraints will still be created, though.
>
> --
> Erik
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump and restore without indexes
2024-06-04 17:42 pg_dump and restore without indexes Teja Jakkidi <teja.jakkidi05@gmail.com>
2024-06-04 17:56 ` Re: pg_dump and restore without indexes Erik Wienhold <ewie@ewie.name>
2024-06-04 17:58 ` Re: pg_dump and restore without indexes Teja Jakkidi <teja.jakkidi05@gmail.com>
@ 2024-06-04 18:27 ` Erik Wienhold <ewie@ewie.name>
2024-06-04 18:30 ` Re: pg_dump and restore without indexes Teja Jakkidi <teja.jakkidi05@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Erik Wienhold @ 2024-06-04 18:27 UTC (permalink / raw)
To: Teja Jakkidi <teja.jakkidi05@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
On 2024-06-04 19:58 +0200, Teja Jakkidi wrote:
> Also, is there a way we can remap schema or table during restore like
> how we have an option to remap in Oracle?
Not in pg_dump or pg_restore. Maybe some third-party tool, but I don't
know.
I had to do this in the past and just renamed the schemas after
restoring into a new database. Using a find-and-replace on the SQL dump
might also work (maybe with a clever regexp) but it's not foolproof if
the search matches false-positives in data segments or string literals.
--
Erik
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump and restore without indexes
2024-06-04 17:42 pg_dump and restore without indexes Teja Jakkidi <teja.jakkidi05@gmail.com>
2024-06-04 17:56 ` Re: pg_dump and restore without indexes Erik Wienhold <ewie@ewie.name>
2024-06-04 17:58 ` Re: pg_dump and restore without indexes Teja Jakkidi <teja.jakkidi05@gmail.com>
2024-06-04 18:27 ` Re: pg_dump and restore without indexes Erik Wienhold <ewie@ewie.name>
@ 2024-06-04 18:30 ` Teja Jakkidi <teja.jakkidi05@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Teja Jakkidi @ 2024-06-04 18:30 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Thank you for your inputs, Erik.
Regards,
J. Teja.
> On Jun 4, 2024, at 11:27 AM, Erik Wienhold <ewie@ewie.name> wrote:
>
> On 2024-06-04 19:58 +0200, Teja Jakkidi wrote:
>> Also, is there a way we can remap schema or table during restore like
>> how we have an option to remap in Oracle?
>
> Not in pg_dump or pg_restore. Maybe some third-party tool, but I don't
> know.
>
> I had to do this in the past and just renamed the schemas after
> restoring into a new database. Using a find-and-replace on the SQL dump
> might also work (maybe with a clever regexp) but it's not foolproof if
> the search matches false-positives in data segments or string literals.
>
> --
> Erik
^ permalink raw reply [nested|flat] 6+ messages in thread
* pg_dump and restore without indexes
2024-06-04 17:42 pg_dump and restore without indexes Teja Jakkidi <teja.jakkidi05@gmail.com>
@ 2024-06-04 18:15 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
1 sibling, 0 replies; 6+ messages in thread
From: Wetmore, Matthew (CTR) @ 2024-06-04 18:15 UTC (permalink / raw)
To: Teja Jakkidi <teja.jakkidi05@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
This is where custom scripting your dump comes in handy.
Inside the shell script you can dump only what you want. Specifically line by line. This is good because you don't have to touch the 'official dump' and can have all your stuff editable and restorable if needed.
Most large db's do this since order of operations can cause issues.
a separate for loop for indexes, tables, views, sequences, etc.
-----Original Message-----
From: Teja Jakkidi <teja.jakkidi05@gmail.com>
Sent: Tuesday, June 4, 2024 10:42 AM
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] pg_dump and restore without indexes
Hello Admins,
I am trying to look for an option that can be added in pg_dump command to ignore all the indexes when creating the schema dump with data.
Is there any such option that can be used in pg_dump? Or in pg_restore?
Please help with your inputs.
Thanks in advance,
J. Teja.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-06-04 18:30 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-04 17:42 pg_dump and restore without indexes Teja Jakkidi <teja.jakkidi05@gmail.com>
2024-06-04 17:56 ` Erik Wienhold <ewie@ewie.name>
2024-06-04 17:58 ` Teja Jakkidi <teja.jakkidi05@gmail.com>
2024-06-04 18:27 ` Erik Wienhold <ewie@ewie.name>
2024-06-04 18:30 ` Teja Jakkidi <teja.jakkidi05@gmail.com>
2024-06-04 18:15 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.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