Thread: 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