Обсуждение: Archeiving and Purging
Dear Users, I have lots of data in my DB. I need to do archeiving and purging of my data. Can anyone please help me with step by step riles? Thanks in Advance. Aditya Kumar -- View this message in context: http://postgresql.nabble.com/Archeiving-and-Purging-tp5842393.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 3/18/2015 7:20 AM, adityagis wrote: > I have lots of data in my DB. I need to do archeiving and purging of my > data. > Can anyone please help me with step by step riles? like this? select * from table where datefield < current_date-interval '6 months'; delete from table where datefield < current_date-interval '6 months'; obviously, save the data you selected in a suitable archive file. repeat this for each table you wish to 'archive and purge'. alter the interval with whatever criteria you want to use for this archive and purge operation. -- john r pierce, from the mid left coast
On Wednesday, March 18, 2015, John R Pierce <pierce@hogranch.com> wrote:
On 3/18/2015 7:20 AM, adityagis wrote:I have lots of data in my DB. I need to do archeiving and purging of my
data.
Can anyone please help me with step by step riles?
like this?
select * from table where datefield < current_date-interval '6 months';
delete from table where datefield < current_date-interval '6 months';
obviously, save the data you selected in a suitable archive file. repeat this for each table you wish to 'archive and purge'.
alter the interval with whatever criteria you want to use for this archive and purge operation.
Or, pg_dump followed by a drop database...maybe followed by pg_restore (schema only)...
Probably suggest a COPY ... TO, via psql or or the server depending, if doing a partial archive.
Not sure what step-by_step commands should be used given the lack of o/s, client libraries, or intended result specifics. The documentation, postgresql and/or operating system, will tell how but passing in the proper "arguments" requires more knowledge than has been provided.
On 03/18/2015 09:20 AM, adityagis wrote: > Dear Users, > I have lots of data in my DB. I need to do archeiving and purging of my > data. > Can anyone please help me with step by step riles? > > > Thanks in Advance. > Aditya Kumar > > > > -- > View this message in context: http://postgresql.nabble.com/Archeiving-and-Purging-tp5842393.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > This is where partitioning comes in: https://github.com/keithf4/pg_partman After that is set up, you would only need to drop the partition which would take only a second or so. Problem is, it will probably take awhile to get the data into a partitioned scheme. Of course, I don't know what you mean by "lots of data". Normally, just using a delete statement will be fine up until about 100 million rows or so (on decent hardware). --Sam
HI David/John, Thanks for the support. But as of now I have not done any configuaration for the archieving process. I hope once my configuarations are done then may be I can proceed with your suggestions. I am still looking for the intial process that need to be performed for archeiving. Thanks Aditya Kumar -- View this message in context: http://postgresql.nabble.com/Archeiving-and-Purging-tp5842393p5842524.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 03/18/2015 11:38 PM, adityagis wrote: > HI David/John, > Thanks for the support. > But as of now I have not done any configuaration for the archieving process. > I hope once my configuarations are done then may be I can proceed with your > suggestions. > > I am still looking for the intial process that need to be performed for > archeiving. There is no built in archiving process in Postgres. Archiving, if desired, is left up to the user as there is no 'one way'. > > > Thanks > Aditya Kumar > > > > -- > View this message in context: http://postgresql.nabble.com/Archeiving-and-Purging-tp5842393p5842524.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com