Обсуждение: Problem with backing up a large database

classmarkets GmbH | Schumannstraße 6 | 10117 Berlin | Deutschland
Tel: +49 (0)30 56 59 001-0 | Fax: +49 (0)30 56 59 001-99 | www.classmarkets.com

Amtsgericht Charlottenburg HRB 111815 B | USt.Id.Nr: DE 260731582
Geschäftsführer: Veit Mürz, Fabian Ströhle

Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sie darf ausschließlich durch den vorgesehenen Empfänger und Adressaten gelesen, kopiert oder genutzt werden. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Jede unerlaubte Nutzung oder Weitergabe des Inhalts dieser Nachricht, sei es vollständig oder teilweise, ist unzulässig. Bitte beachten Sie, dass E-Mail-Nachrichten an den Absender nicht für fristgebundene Mitteilungen geeignet sind. Fristgebundene Mitteilungen sind daher ausschließlich per Post oder per Telefax zu übersenden.

2013/8/20 Athanasios Kostopoulos <athanasios.kostopoulos@classmarkets.com>:
> Dear all,
>
> I am trying to dump a large (appx 200Gb) database and exclude certain tables
> from it. The command I am using is the following (with IP/Port and DBname
> masked for obvious reasons:)
>
> pg_dump -h XXX.XXX.XXX.XXX -p XXX -U postgres  -T feedimport_log -T
> error_item -T '^z*' -Fc some_large_db


Works for me on 9.2.4.
I'm not sure the regular expression is correct, so I would try only
with 'z*' (as it is I read it as something that matches any z).

Luca

classmarkets GmbH | Schumannstraße 6 | 10117 Berlin | Deutschland
Tel: +49 (0)30 56 59 001-0 | Fax: +49 (0)30 56 59 001-99 | www.classmarkets.com

Amtsgericht Charlottenburg HRB 111815 B | USt.Id.Nr: DE 260731582
Geschäftsführer: Veit Mürz, Fabian Ströhle

Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sie darf ausschließlich durch den vorgesehenen Empfänger und Adressaten gelesen, kopiert oder genutzt werden. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Jede unerlaubte Nutzung oder Weitergabe des Inhalts dieser Nachricht, sei es vollständig oder teilweise, ist unzulässig. Bitte beachten Sie, dass E-Mail-Nachrichten an den Absender nicht für fristgebundene Mitteilungen geeignet sind. Fristgebundene Mitteilungen sind daher ausschließlich per Post oder per Telefax zu übersenden.

On 08/20/2013 01:47 PM, Athanasios Kostopoulos wrote:
> Thank you for your reply Luca. Regarding the regular expression, I
> need to exclude all tables starting with z thus ^z. I am not sure
> about the star, should I do something along the lines of
> ^z[Aa-Zz|0-9]* to catch all cases?

Just do ^z and leave the star out of it.

--
Vik

Vik Fearing <vik.fearing@dalibo.com> writes:
> On 08/20/2013 01:47 PM, Athanasios Kostopoulos wrote:
>> Thank you for your reply Luca. Regarding the regular expression, I
>> need to exclude all tables starting with z thus ^z. I am not sure
>> about the star, should I do something along the lines of
>> ^z[Aa-Zz|0-9]* to catch all cases?

> Just do ^z and leave the star out of it.

I think it would be '[^z]*'.  The basic notation is not regex, it's
like Unix shells' filename wildcards; so you *do* need a star.
But we do recognize regex-style character classes, else this
requirement couldn't be met at all.

            regards, tom lane

Hi all,

2013/8/20 Tom Lane <tgl@sss.pgh.pa.us>:
>
> I think it would be '[^z]*'.  The basic notation is not regex, it's
> like Unix shells' filename wildcards; so you *do* need a star.
> But we do recognize regex-style character classes, else this
> requirement couldn't be met at all.
>

Agreed that the notation is not regex, and that the star is required,
but '[^z]*' does not seem to address Athanasios need to me :

$ pg_dump -s -T '[^z]*' pgbench | grep "^CREATE TABLE" | cut -d" " -f3
ztest

--> revert the "table name begins with a z" condition, and thus
excludes everything except for the "ztest" table.
--> same behaviour that : pg_dump -t 'z*'

$ pg_dump -s -T '^z*' pgbench | grep "^CREATE TABLE" | cut -d" " -f3
pgbench_accounts
pgbench_branches
pgbench_history
pgbench_tellers

--> excludes tables using "table name begins with a z" condition, and
thus do what Athanasios was expecting.
--> for that matter, the "^" does not seem to be necessary, pg_dump -T
'z*' has the same behaviour

But I may have missed your point.


2013/8/20 Athanasios Kostopoulos <athanasios.kostopoulos@classmarkets.com>:
>
> pg_dump -h XXX.XXX.XXX.XXX -p XXX -U postgres  -T feedimport_log -T
> error_item -T '^z*' -Fc some_large_db
>
> However, my backups are quite large (thus it takes too much time to process
> them) and when I am doing a listing for tables that should not be there, I
> get the following (among others):
>
> 307; 1259 5501792 TABLE feedimport feedimport_log postgres
>
> as well as the whole bunch of tables starting with z that I am trying to
> exclude using the regex.
> Any pointers about what I might be doing wrong and how I can improve the
> backup process?


Strange, from what I can see, this command line should work as you expect.
That may be due to the schema feedimport not beeing in your search_path.

--
Mael

Mael Rimbault <mael.rimbault@gmail.com> writes:
> 2013/8/20 Tom Lane <tgl@sss.pgh.pa.us>:
>> I think it would be '[^z]*'.

> Agreed that the notation is not regex, and that the star is required,
> but '[^z]*' does not seem to address Athanasios need to me :
> $ pg_dump -s -T '[^z]*' pgbench | grep "^CREATE TABLE" | cut -d" " -f3

Ah, I was thinking of using the pattern with -t.  If you want to use -T
then yeah, you'd invert the sense.

            regards, tom lane