Thread: Restoring 2 Tables From All Databases Backup
Dear all, About 1 month ago, I take a complete databases backup of my Database server through pg_dumpall command. Today I need to extract or restore only 2 tables in a database. Is it possible or I have to restore complete Databases again. Size of backup is 10 GB in .sql.gz format. Please let me know how to extract the tables from this 10Gb backup file Thanks
2011/10/5 Adarsh Sharma <adarsh.sharma@orkash.com>: > About 1 month ago, I take a complete databases backup of my Database server > through pg_dumpall command. > Today I need to extract or restore only 2 tables in a database. > > Is it possible or I have to restore complete Databases again. Size of backup > is 10 GB in .sql.gz format. If your dump was created using custom format [1] (pg_dump --format=custom or -Fc) you can do a pg_restore using --use-list and --list [2]. [1] http://www.postgresql.org/docs/current/interactive/app-pgdump.html#PG-DUMP-OPTIONS [2] http://www.postgresql.org/docs/current/interactive/app-pgrestore.html#APP-PGRESTORE-OPTIONS -- Dickson S. Guedes mail/xmpp: guedes@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br
2011/10/5 Adarsh Sharma <adarsh.sharma@orkash.com>
since this is a plaintext file, not a custom format backup,
you unfortunately need to extract portions of text using some editor or program...
for this kind of work I would recommend Perl or Awk.
below is my "first shot" - thats incomplete (no restoration of indexes/sequences):
gunzip -cd all.sql.gz | awk '/^CREATE TABLE mytable /,/^$/ { print }; /^COPY mytable /,/^$/ { print };'
which does print all lines from CREATE TABLE mytable to next empty line, and all lines from COPY mytable to next empty line.
Dear all,
About 1 month ago, I take a complete databases backup of my Database server through pg_dumpall command.
Today I need to extract or restore only 2 tables in a database.
Is it possible or I have to restore complete Databases again. Size of backup is 10 GB in .sql.gz format.
Please let me know how to extract the tables from this 10Gb backup file
since this is a plaintext file, not a custom format backup,
you unfortunately need to extract portions of text using some editor or program...
for this kind of work I would recommend Perl or Awk.
below is my "first shot" - thats incomplete (no restoration of indexes/sequences):
gunzip -cd all.sql.gz | awk '/^CREATE TABLE mytable /,/^$/ { print }; /^COPY mytable /,/^$/ { print };'
which does print all lines from CREATE TABLE mytable to next empty line, and all lines from COPY mytable to next empty line.
You should to create new database with two empty tables, set access rights for all schemas readonly and pipe backup to this database. 2011/10/5, Dickson S. Guedes <listas@guedesoft.net>: > 2011/10/5 Adarsh Sharma <adarsh.sharma@orkash.com>: >> About 1 month ago, I take a complete databases backup of my Database >> server >> through pg_dumpall command. >> Today I need to extract or restore only 2 tables in a database. >> >> Is it possible or I have to restore complete Databases again. Size of >> backup >> is 10 GB in .sql.gz format. > > If your dump was created using custom format [1] (pg_dump > --format=custom or -Fc) you can do a pg_restore using --use-list and > --list [2]. > > [1] > http://www.postgresql.org/docs/current/interactive/app-pgdump.html#PG-DUMP-OPTIONS > [2] > http://www.postgresql.org/docs/current/interactive/app-pgrestore.html#APP-PGRESTORE-OPTIONS > > -- > Dickson S. Guedes > mail/xmpp: guedes@guedesoft.net - skype: guediz > http://guedesoft.net - http://www.postgresql.org.br > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- ------------ pasman
Hi Adarsh,
Following link will help you:-
2011/10/5 pasman pasmański <pasman.p@gmail.com>
You should to create new database with two empty tables, set access
rights for all schemas readonly and pipe backup to this database.
2011/10/5, Dickson S. Guedes <listas@guedesoft.net>:--> 2011/10/5 Adarsh Sharma <adarsh.sharma@orkash.com>:
>> About 1 month ago, I take a complete databases backup of my Database
>> server
>> through pg_dumpall command.
>> Today I need to extract or restore only 2 tables in a database.
>>
>> Is it possible or I have to restore complete Databases again. Size of
>> backup
>> is 10 GB in .sql.gz format.
>
> If your dump was created using custom format [1] (pg_dump
> --format=custom or -Fc) you can do a pg_restore using --use-list and
> --list [2].
>
> [1]
> http://www.postgresql.org/docs/current/interactive/app-pgdump.html#PG-DUMP-OPTIONS
> [2]
> http://www.postgresql.org/docs/current/interactive/app-pgrestore.html#APP-PGRESTORE-OPTIONS
>
> --
> Dickson S. Guedes
> mail/xmpp: guedes@guedesoft.net - skype: guediz
> http://guedesoft.net - http://www.postgresql.org.br
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
------------
pasman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks to all, the problem is solved now.
But Still I donot know how to use existing data directory (near about 110GB) in a new Postgres Installation.
I ask this in the list yesterday but still no clue on this.
Please guide if it is possible.
Best Regards
Adarsh
Raghavendra wrote:
But Still I donot know how to use existing data directory (near about 110GB) in a new Postgres Installation.
I ask this in the list yesterday but still no clue on this.
Please guide if it is possible.
Best Regards
Adarsh
Raghavendra wrote:
Hi Adarsh,Filip workaround is right approach, since its plain text format you need to play with SED/AWK to pull those two tables.Following link will help you:-2011/10/5 pasman pasmański <pasman.p@gmail.com>You should to create new database with two empty tables, set access
rights for all schemas readonly and pipe backup to this database.
2011/10/5, Dickson S. Guedes <listas@guedesoft.net>:--> 2011/10/5 Adarsh Sharma <adarsh.sharma@orkash.com>:
>> About 1 month ago, I take a complete databases backup of my Database
>> server
>> through pg_dumpall command.
>> Today I need to extract or restore only 2 tables in a database.
>>
>> Is it possible or I have to restore complete Databases again. Size of
>> backup
>> is 10 GB in .sql.gz format.
>
> If your dump was created using custom format [1] (pg_dump
> --format=custom or -Fc) you can do a pg_restore using --use-list and
> --list [2].
>
> [1]
> http://www.postgresql.org/docs/current/interactive/app-pgdump.html#PG-DUMP-OPTIONS
> [2]
> http://www.postgresql.org/docs/current/interactive/app-pgrestore.html#APP-PGRESTORE-OPTIONS
>
> --
> Dickson S. Guedes
> mail/xmpp: guedes@guedesoft.net - skype: guediz
> http://guedesoft.net - http://www.postgresql.org.br
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
------------
pasman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2011/10/6 Adarsh Sharma <adarsh.sharma@orkash.com>
You need to learn more about postgres server mechanics... especially initdb, pg_ctl, starting and stopping server and so on. See http://www.postgresql.org/docs/9.1/static/runtime.html, http://www.postgresql.org/docs/9.1/static/runtime-config-file-locations.html .
To use existing data directory in new installation, you can just stop the server, replace data_directory, and start the server. Remember about file permissions - data_directory must be owned by server process owner ("postgres") and chmod 700.
Filip
Thanks to all, the problem is solved now.
But Still I donot know how to use existing data directory (near about 110GB) in a new Postgres Installation.
I ask this in the list yesterday but still no clue on this.
Please guide if it is possible.
You need to learn more about postgres server mechanics... especially initdb, pg_ctl, starting and stopping server and so on. See http://www.postgresql.org/docs/9.1/static/runtime.html, http://www.postgresql.org/docs/9.1/static/runtime-config-file-locations.html .
To use existing data directory in new installation, you can just stop the server, replace data_directory, and start the server. Remember about file permissions - data_directory must be owned by server process owner ("postgres") and chmod 700.
Filip
On 06/10/2011 11:34, Filip Rembiałkowski wrote:
> To use existing data directory in new installation, you can just stop
> the server, replace data_directory, and start the server. Remember about
> file permissions - data_directory must be owned by server process owner
> ("postgres") and chmod 700.
>
BUT - and this is a big but - the data *must* be from the same Postgres
major version, same architecture, etc.
This might have been mentioned upthread, but it's worth repeating.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
That's the bottleneck I need to solve:-
Previous data & Os Version :- Postgresql-8.3 and Suse Enterprise Linux
New Data & OS Version :- Postgresql-8.4 and Ubuntu 10.04
What to do know?
Thanks
Raymond O'Donnell wrote:
Previous data & Os Version :- Postgresql-8.3 and Suse Enterprise Linux
New Data & OS Version :- Postgresql-8.4 and Ubuntu 10.04
What to do know?
Thanks
Raymond O'Donnell wrote:
On 06/10/2011 11:34, Filip Rembiałkowski wrote:To use existing data directory in new installation, you can just stop the server, replace data_directory, and start the server. Remember about file permissions - data_directory must be owned by server process owner ("postgres") and chmod 700.BUT - and this is a big but - the data *must* be from the same Postgres major version, same architecture, etc. This might have been mentioned upthread, but it's worth repeating. Ray.
follow the other thread that you started. there are some suggestions for you.
2011/10/6 Adarsh Sharma <adarsh.sharma@orkash.com>
That's the bottleneck I need to solve:-
Previous data & Os Version :- Postgresql-8.3 and Suse Enterprise Linux
New Data & OS Version :- Postgresql-8.4 and Ubuntu 10.04
What to do know?
Thanks
Raymond O'Donnell wrote:On 06/10/2011 11:34, Filip Rembiałkowski wrote:To use existing data directory in new installation, you can just stop the server, replace data_directory, and start the server. Remember about file permissions - data_directory must be owned by server process owner ("postgres") and chmod 700.BUT - and this is a big but - the data *must* be from the same Postgres major version, same architecture, etc. This might have been mentioned upthread, but it's worth repeating. Ray.