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 (or
a database) using the “*date_trunc('day', NOW() - interval '1 month');*”
as a where clause. I have read online that this is achievable with the “t”
option and I have tried the below command

pg_dump --table=re_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*

​There is no provision in pg_dump to add custom where clauses in the 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.

​The 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.​

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 (or a database) using the “date_trunc('day', NOW() - interval '1 month');” as a where clause. I have read online that this is achievable with the “t” option and I have tried the below command
pg_dump --table=re_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

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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 (or
a database) using the “*date_trunc('day', NOW() - interval '1 month');*”
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

Thank You Bricklen, Let me check this out,

Basically my requirement is, I have a live Db with certain tables and a backup Db at another location (both on postgressql). I need to take a backup of this live DB every night for the previous day (i.e the backup script running on 07/07/2016 will take the backup of the DB for 06/07/2016). This backup will be then transferred to the backup DB server and will be inserted into that DB. From what I have read pg_dump is the solution (similar to export in oracle), do you think of any other approach to get to this objective, have you come across a script or something that already does this,

Thanks once again for the support,

Prashanth Adiyodi
Technical Account Manager
Skype: prashanth.adiyodi
Mob: +91-9819488395
[celltick]

From: bricklen [mailto:bricklen@gmail.com]
Sent: Thursday, July 07, 2016 9:40 PM
To: Prashanth Adiyodi
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Where clause in pg_dump: need help

On Thu, Jul 7, 2016 at 7:06 AM, Prashanth Adiyodi <Prashantha@celltick.com<mailto:Prashantha@celltick.com>> wrote:
I am working on a script where I need to take backup of certain tables (or a database) using the “date_trunc('day', NOW() - interval '1 month');” 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

1.- CCing to the list ( remember to hit reply-all or whatever your MUA
uses for that, otherwise threads may get lost ).

2.- Try to avoid top-posting, it's not the style of the list ( or mine ).

On Fri, Jul 8, 2016 at 4:43 AM, Prashanth Adiyodi
<Prashantha@celltick.com> wrote:

Basically my requirement is, I have a live Db with certain tables and a backup Db at another location (both on postgressql). I need to take a backup of this live DB every night for the previous day (i.e the backup script running on 07/07/2016 will take the backup of the DB for 06/07/2016). This backup will be then transferred to the backup DB server and will be inserted into that DB. From what I have read pg_dump is the solution (similar to export in oracle), do you think of any other approach to get to this objective, have you come across a script or something that already does this,

Your requirement is a bit 'understated'. I assume your problem is:

1.- You have a backup with a series of tables which get inserted WITH
a timestamp.
2.- At the end of the day you want to transfer the inserted data, and
only the inserted data, to another server and insert it ther.

If BOTH servers are postgres, you can do it easily with a series of
COPY commands easily. If the target one is not postgres I would use it
too, but pass the COPY data through a perl script to generate whatever
syntax the target DB needs ( I've done that to go from postgres to sql
server and back using freebcp, IIRC, on the sql server side )

You still can have problems IF you have updates to the tables, or
deletions, or <insert your favorite problematic operation here>. But
if you just have insertions, copy is easy to do.

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Jul 8, 2016 at 5:38 PM Francisco Olarte <folarte@peoplecall.com>
wrote:

1.- CCing to the list ( remember to hit reply-all or whatever your MUA
uses for that, otherwise threads may get lost ).

2.- Try to avoid top-posting, it's not the style of the list ( or mine ).

On Fri, Jul 8, 2016 at 4:43 AM, Prashanth Adiyodi
<Prashantha@celltick.com> wrote:

Basically my requirement is, I have a live Db with certain tables and a

backup Db at another location (both on postgressql).

Both databases are PostgreSQL (?). What version?

I need to take a backup of this live DB every night for the previous day
(i.e the backup script running on 07/07/2016 will take the backup of the DB
for 06/07/2016).

Does this need to be done for one table or multiple tables?

This backup will be then transferred to the backup DB server and will be
inserted into that DB.

What will you be doing on the target database? Is it a read-only database?

From what I have read pg_dump is the solution (similar to export in
oracle), do you think of any other approach to get to this objective, have
you come across a script or something that already does this,

May be you can use
psql -c "COPY (SELECT .. WHERE..) TO stdout" | psql -c "COPY (mytable) FROM
stdin"

OR
Setup replication and have a scheduled script to set recovery_target_time
and puase_at_recovery_target to effectively replicate changes from one DB
to other DB and maintaining a gap. But then the targetDB would be a read
only replica and needs to be exactly same at the main DB/sourceDB

You need to explain more on version of the database, what exactly you aim
at doing with the target DB.

Your requirement is a bit 'understated'. I assume your problem is:

1.- You have a backup with a series of tables which get inserted WITH
a timestamp.
2.- At the end of the day you want to transfer the inserted data, and
only the inserted data, to another server and insert it ther.

If BOTH servers are postgres, you can do it easily with a series of
COPY commands easily. If the target one is not postgres I would use it
too, but pass the COPY data through a perl script to generate whatever
syntax the target DB needs ( I've done that to go from postgres to sql
server and back using freebcp, IIRC, on the sql server side )

You still can have problems IF you have updates to the tables, or
deletions, or <insert your favorite problematic operation here>. But
if you just have insertions, copy is easy to do.

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

Hi Sameer, Please see comments inline

Prashanth Adiyodi
Technical Account Manager
Skype: prashanth.adiyodi
Mob: +91-9819488395
[celltick]

From: Sameer Kumar [mailto:sameer.kumar@ashnik.com]
Sent: Friday, July 08, 2016 3:18 PM
To: Francisco Olarte; Prashanth Adiyodi
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] [BUGS] Where clause in pg_dump: need help

On Fri, Jul 8, 2016 at 5:38 PM Francisco Olarte <folarte@peoplecall.com<mailto:folarte@peoplecall.com>> wrote:
1.- CCing to the list ( remember to hit reply-all or whatever your MUA
uses for that, otherwise threads may get lost ).

2.- Try to avoid top-posting, it's not the style of the list ( or mine ).

On Fri, Jul 8, 2016 at 4:43 AM, Prashanth Adiyodi
<Prashantha@celltick.com<mailto:Prashantha@celltick.com>> wrote:

Basically my requirement is, I have a live Db with certain tables and a backup Db at another location (both on postgressql).

Both databases are PostgreSQL (?). What version?
Yes, Both are postgres SQL, ver 9.3.4

I need to take a backup of this live DB every night for the previous day (i.e the backup script running on 07/07/2016 will take the backup of the DB for 06/07/2016).

Does this need to be done for one table or multiple tables?
Multiple tables

This backup will be then transferred to the backup DB server and will be inserted into that DB.

What will you be doing on the target database? Is it a read-only database?
It is not a read only database

From what I have read pg_dump is the solution (similar to export in oracle), do you think of any other approach to get to this objective, have you come across a script or something that already does this,

May be you can use
psql -c "COPY (SELECT .. WHERE..) TO stdout" | psql -c "COPY (mytable) FROM stdin"

OR
Setup replication and have a scheduled script to set recovery_target_time and puase_at_recovery_target to effectively replicate changes from one DB to other DB and maintaining a gap. But then the targetDB would be a read only replica and needs to be exactly same at the main DB/sourceDB

You need to explain more on version of the database, what exactly you aim at doing with the target DB.
Hi, the requirement is this, I have multiple tables where there may not be a timestamp column. I need to run a script which will execute post-midnight say at 2 AM and create a dump file (say data.sql), which will have records for all the previous day. I will then transfer this file to the target server and dump this data there, the idea is to create two copies of the data in case of a disaster on the original database server.

Your requirement is a bit 'understated'. I assume your problem is:

1.- You have a backup with a series of tables which get inserted WITH
a timestamp.
2.- At the end of the day you want to transfer the inserted data, and
only the inserted data, to another server and insert it ther.

If BOTH servers are postgres, you can do it easily with a series of
COPY commands easily. If the target one is not postgres I would use it
too, but pass the COPY data through a perl script to generate whatever
syntax the target DB needs ( I've done that to go from postgres to sql
server and back using freebcp, IIRC, on the sql server side )

You still can have problems IF you have updates to the tables, or
deletions, or <insert your favorite problematic operation here>. But
if you just have insertions, copy is easy to do.

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com<http://www.ashnik.com&gt;

On Fri, 8 Jul 2016, 8:06 p.m. Prashanth Adiyodi, <Prashantha@celltick.com>
wrote:

Hi Sameer, Please see comments inline

*Prashanth Adiyodi *

*Technical Account Manager*

*Skype: prashanth.adiyodi*

*Mob: +91-9819488395*

[image: celltick]

*From:* Sameer Kumar [mailto:sameer.kumar@ashnik.com]
*Sent:* Friday, July 08, 2016 3:18 PM
*To:* Francisco Olarte; Prashanth Adiyodi
*Cc:* pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] [BUGS] Where clause in pg_dump: need help

On Fri, Jul 8, 2016 at 5:38 PM Francisco Olarte <folarte@peoplecall.com>
wrote:

1.- CCing to the list ( remember to hit reply-all or whatever your MUA
uses for that, otherwise threads may get lost ).

2.- Try to avoid top-posting, it's not the style of the list ( or mine ).

On Fri, Jul 8, 2016 at 4:43 AM, Prashanth Adiyodi
<Prashantha@celltick.com> wrote:

Basically my requirement is, I have a live Db with certain tables and a

backup Db at another location (both on postgressql).

Both databases are PostgreSQL (?). What version?

Yes, Both are postgres SQL, ver 9.3.4

I need to take a backup of this live DB every night for the previous day
(i.e the backup script running on 07/07/2016 will take the backup of the DB
for 06/07/2016).

Does this need to be done for one table or multiple tables?

Multiple tables

This backup will be then transferred to the backup DB server and will be
inserted into that DB.

What will you be doing on the target database? Is it a read-only database?

It is not a read only database

From what I have read pg_dump is the solution (similar to export in
oracle), do you think of any other approach to get to this objective, have
you come across a script or something that already does this,

May be you can use
psql -c "COPY (SELECT .. WHERE..) TO stdout" | psql -c "COPY (mytable)
FROM stdin"

OR
Setup replication and have a scheduled script to set recovery_target_time
and puase_at_recovery_target to effectively replicate changes from one DB
to other DB and maintaining a gap. But then the targetDB would be a read
only replica and needs to be exactly same at the main DB/sourceDB

You need to explain more on version of the database, what exactly you aim
at doing with the target DB.

Hi, the requirement is this, I have multiple tables where there may not be
a timestamp column. I need to run a script which will execute post-midnight
say at 2 AM and create a dump file (say data.sql), which will have records
for all the previous day. I will then transfer this file to the target
server and dump this data there, the idea is to create two copies of the
data in case of a disaster on the original database server.

So it is more like a DR server which always lags behind the master by a day
or is at mid night time of previous day.

Above you have mentioned this target db server (which I assume serves the
purpose of DR) is not read-only(?). What kind of writes will you be doing
on this servers?

Your requirement is a bit 'understated'. I assume your problem is:

1.- You have a backup with a series of tables which get inserted WITH
a timestamp.
2.- At the end of the day you want to transfer the inserted data, and
only the inserted data, to another server and insert it ther.

If BOTH servers are postgres, you can do it easily with a series of
COPY commands easily. If the target one is not postgres I would use it
too, but pass the COPY data through a perl script to generate whatever
syntax the target DB needs ( I've done that to go from postgres to sql
server and back using freebcp, IIRC, on the sql server side )

You still can have problems IF you have updates to the tables, or
deletions, or <insert your favorite problematic operation here>. But
if you just have insertions, copy is easy to do.

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

--

Best Regards

Sameer Kumar | DB Solution Architect

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

Hi Franciso, My comments below inline

-----Original Message-----
From: Francisco Olarte [mailto:folarte@peoplecall.com]
Sent: Friday, July 08, 2016 3:07 PM
To: Prashanth Adiyodi
Cc: pgsql-general@postgresql.org
Subject: Re: [BUGS] Where clause in pg_dump: need help

1.- CCing to the list ( remember to hit reply-all or whatever your MUA uses for that, otherwise threads may get lost ).

2.- Try to avoid top-posting, it's not the style of the list ( or mine ).

On Fri, Jul 8, 2016 at 4:43 AM, Prashanth Adiyodi <Prashantha@celltick.com> wrote:

Basically my requirement is, I have a live Db with certain tables and
a backup Db at another location (both on postgressql). I need to take
a backup of this live DB every night for the previous day (i.e the
backup script running on 07/07/2016 will take the backup of the DB for
06/07/2016). This backup will be then transferred to the backup DB
server and will be inserted into that DB. From what I have read
pg_dump is the solution (similar to export in oracle), do you think of
any other approach to get to this objective, have you come across a
script or something that already does this,

Your requirement is a bit 'understated'. I assume your problem is:

1.- You have a backup with a series of tables which get inserted WITH a timestamp.
Adi-The series of tables may or may not have timestamp
2.- At the end of the day you want to transfer the inserted data, and only the inserted data, to another server and insert it ther.
Adi-Exactly., somewhere post midnight I need to transfer the inserted data for the day to another DB.

If BOTH servers are postgres, you can do it easily with a series of COPY commands easily. If the target one is not postgres I would use it too, but pass the COPY data through a perl script to generate whatever syntax the target DB needs ( I've done that to go from postgres to sql server and back using freebcp, IIRC, on the sql server side )

You still can have problems IF you have updates to the tables, or deletions, or <insert your favorite problematic operation here>. But if you just have insertions, copy is easy to do.
Adi-
I am OK with the copy command, however I am not able to understand (my bad, I am not used to postgres and using for the 1st time) the where clause that should be used to achieve this result.

I tries using something like the below,
psql -d my_db -c 'copy (select * from mytab WHERE date_trunc('day',NOW() - interval '1 day') TO STDOUT' -o data1.copy;

but this, I am sure has some syntax errors, could you help correct this,

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Jul 10, 2016 at 12:53 PM, Prashanth Adiyodi <Prashantha@celltick.com

wrote:

1.- You have a backup with a series of tables which get inserted WITH a
timestamp.
Adi-The series of tables may or may not have timestamp

​Then I think you cannot do what you want using only built-in PostgreSQL
capabilities. Meta data
​about when a record was inserted and/or delete is not kept by the system.

I'm not familiar with the capabilities of logical replication so that may
provide an answer I am unaware of.

Otherwise the only thing that comes to mind is to stream WAL to an
intermediate server and then every so often (once a day) release to the
other server all WAL that accumulated during the previous period.

None of \copy, COPY (SQL), or pg_dump will get you what you want - the
persisted data doesn't contain the information you desired.

Alternatively, once a week (give or take) you could perform a base backup
of the DB. Stream all archive files somewhere and each day perform a
"point in time" recovery.

David J.

Hi Adi: ( Is this correct? Adi is what your message uses to mark your replies ).

On Sun, Jul 10, 2016 at 6:53 PM, Prashanth Adiyodi
<Prashantha@celltick.com> wrote:

Hi Franciso, My comments below inline

Got them. Only problem is your MUA does not signal quotes. It looks
like some kind of outlook by the headers it sends, so I assume it is
normal, I will try to correct it.

1.- You have a backup with a series of tables which get inserted WITH a timestamp.

Adi-The series of tables may or may not have timestamp

2.- At the end of the day you want to transfer the inserted data, and only the inserted data, to another server and insert it ther.

Adi-Exactly., somewhere post midnight I need to transfer the inserted data for the day to another DB.

Then you NEED some kind of marker. The tables WITHOUT timestamp (
point 1 above ) are going to be difficult.

For the discussion I assume you are somehow capable of making a SELECT
query which identifies inserted data for the day, and that either you
do not care about updates/deletions ( not having any is a subset of
this condition ) or you can do a query for those too.

Adi- I am OK with the copy command, however I am not able to understand (my bad, I am not used to postgres and using for the 1st time) the where clause that should be used to achieve this result.

Well, then your problem is on the queries. You need to be able to
identify the data inserted yesterday. There is no magic way to do it.
You NEED some kind of timestamp column. If you lack this you can use a
trigger to mark them into auxiliary tables or, if you feel
adventurous, you can try to use the xmin/xmax columns ( I would NOT
recommend even trying that, given you are having problems with simple
select queries ).

I tries using something like the below,
psql -d my_db -c 'copy (select * from mytab WHERE date_trunc('day',NOW() - interval '1 day') TO STDOUT' -o data1.copy;

Your where expresion is a constant of timestamp type, i.e.,
'2016-07-11 12:00:00+00', where needs a BOOLEAN.

YOU need to be able to identify the inserted rows. YOU know your data
definitions. Ar you able to query them ?

but this, I am sure has some syntax errors, could you help correct this,

NOT, because I do not know the table structure. Only you can do that.

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general