Обсуждение: 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 schemadump 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.
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
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
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 haveto 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 schemadump 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.
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
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