How to create an archive for old records?
Hi all,
I am looking for ways to create an archive of records older than 3 months in
one of my table, and store these extracted records into a local database.
Does Postgres have any command to do this?
Thanks.
_________________________________________________________________
Find just what you are after with the more precise, more powerful new
Windows Live Search. http://search.msn.com.sg/ Try it now.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 02/14/07 19:11, carter ck wrote:
Hi all,
I am looking for ways to create an archive of records older than 3
months in one of my table, and store these extracted records into a
local database. Does Postgres have any command to do this?
A single command that will copy data to a destination database, and
then delete from the source database?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFF07V5S9HxQb37XmcRAmcmAKCBRmyMFbhnfC04VUwI29pUDEVpzgCdGeDi
ZqGkW48PU/99qt9bs0waftA=
=V7r+
-----END PGP SIGNATURE-----
Ron Johnson wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1On 02/14/07 19:11, carter ck wrote:
Hi all,
I am looking for ways to create an archive of records older than 3
months in one of my table, and store these extracted records into a
local database. Does Postgres have any command to do this?A single command that will copy data to a destination database, and
then delete from the source database?-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)iD8DBQFF07V5S9HxQb37XmcRAmcmAKCBRmyMFbhnfC04VUwI29pUDEVpzgCdGeDi
ZqGkW48PU/99qt9bs0waftA=
=V7r+
-----END PGP SIGNATURE--------------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
I think the problem may be in determining when a record was added to the
table. If there is no 'date added' column as part of your table
specification that you populate when adding a row then is there any way
to determine when a record was added?
If there is a date added (which is a standard I put in all tables I use)
then it should be a fairly straight forward task of doing an INSERT INTO
followed by a DELETE FROM. As for a single command... I'm not aware of
any "INSERT INTO AND DELETE ORIGINAL" variant so it would have to be two
SQL commands, albeit it uncomplicated.
--
Paul Lambert
Database Administrator
AutoLedgers
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 02/14/07 19:34, Paul Lambert wrote:
Ron Johnson wrote:
On 02/14/07 19:11, carter ck wrote:Hi all,
I am looking for ways to create an archive of records older than 3
months in one of my table, and store these extracted records into a
local database. Does Postgres have any command to do this?A single command that will copy data to a destination database, and
then delete from the source database?I think the problem may be in determining when a record was added to the
table. If there is no 'date added' column as part of your table
specification that you populate when adding a row then is there any way
to determine when a record was added?If there is a date added (which is a standard I put in all tables I use)
then it should be a fairly straight forward task of doing an INSERT INTO
followed by a DELETE FROM. As for a single command... I'm not aware of
any "INSERT INTO AND DELETE ORIGINAL" variant so it would have to be two
SQL commands, albeit it uncomplicated.
But that falls down when you need to archive 60M records and not
block any other jobs.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFF07vkS9HxQb37XmcRAhN+AJ4/KIxnwof9gGa2opz9LlpcKeJjHwCghhGz
oRFZDrAFhphQMpV13l0Brgw=
=TOaV
-----END PGP SIGNATURE-----