Обсуждение: Where clause in pg_dump: need help

On Thu, Jul 7, 2016 at 10:06 AM, Prashanth Adiyodi <Prashantha@celltick.com=
>
wrote:

> Dear Team,
>
>
>
> I am working on a script where I need to take backup of certain tables (o=
r
> a database) using the =E2=80=9C*date_trunc('day', NOW() - interval '1 mon=
th');*=E2=80=9D
> as a where clause. I have read online that this is achievable with the =
=E2=80=9Ct=E2=80=9D
> option and I have tried the below command
>
>
>
> pg_dump --table=3Dre_userstatus --data-only --column-inserts -t
> "date_trunc('day', NOW() - interval '1 month')" comp_db > data1.sql
>
>
>
> but this does not work, could you please help with the correct syntax or
> redirect me to any documentation which implements this, I am a newbee to
> Postgres SQL and I need this help from you, my pg_dump version is
>
>
>
> *pg_dump (PostgreSQL) 9.3.4*
>
>
>
=E2=80=8BThere is no provision in pg_dump to add custom where clauses in th=
e manner
you describe.  If you need a partial dump of a table you will need to write
a psql script and tell it to do exactly what you want.

=E2=80=8BThe options available to you are documented.

https://www.postgresql.org/docs/9.5/static/app-pgdump.html

You will note that your "--table" and your "-t" are the same...all they do
is select (entire) tables.=E2=80=8B

David J.

Hello:

On Thu, Jul 7, 2016 at 4:06 PM, Prashanth Adiyodi
<Prashantha@celltick.com> wrote:
> I am working on a script where I need to take backup of certain tables (o=
r a database) using the =E2=80=9Cdate_trunc('day', NOW() - interval '1 mont=
h');=E2=80=9D as a where clause. I have read online that this is achievable=
 with the =E2=80=9Ct=E2=80=9D option and I have tried the below command
> pg_dump --table=3Dre_userstatus --data-only --column-inserts -t "date_tru=
nc('day', NOW() - interval '1 month')" comp_db > data1.sql
> but this does not work, could you please help with the correct syntax or =
redirect me to any documentation which implements this, I am a newbee to Po=
stgres SQL and I need this help from you, my pg_dump version is

Copying any error message would have been nice. Anyway, AFAIK, -t is a
synonim for --table, and is not used for that.

I see you are trying to dump just one table, as a series of INSERT
commands. I, personally, would recommend to use a COPY using SQL for
that, something along the lines of

psql -c '\copy (select * from re_userstatus WHERE date_trunc('day',
NOW() - interval '1 month').... TO STDOUT' -o data1.copy

The command lets you put any query ( so you can archive
whatevercolumns you want / need )

And these files are easier to work with ( specially in the default tab
delimited format ), are smaller and can easily be transformed to a
INSERT sequence ( or COPY'ed back in in the same table or another one
).

Frnacisco Olarte.

On Thu, Jul 7, 2016 at 7:06 AM, Prashanth Adiyodi <Prashantha@celltick.com>
wrote:

> I am working on a script where I need to take backup of certain tables (o=
r
> a database) using the =E2=80=9C*date_trunc('day', NOW() - interval '1 mon=
th');*=E2=80=9D
> as a where clause.
>

As others have pointed out, pg_dump doesn't have a facility to apply
filters to the dump. However, there is a tool called pg_sample that allows
you to export subsets. https://github.com/mla/pg_sample