Database table archiving I am trying to create a stored procedure (SP) in order to archive a table within my production database which is about 1TB.

The production DB runs on Linux SUSE OS and the destination location for the archive is Windows Server 2012. 

The SP will recurrently archive 42 days of data from the production table to the  archiving destination table and clean up the production table from the archived data to free up space.

I really don't have the skills to write this stored procedure and also put in place a cron job or a script that will perform this task automatically.

Please anyone to help me out with the above?
You will need to create a foreign table in the Linux server that points to the Postgres running on the Windows server. 

Then you can move the rows with an INSERT and a DELETE statement

insert into archive_table (col1, col2, col3)
select c1, c2, c3
from local_table
where created_at_column &lt;= current_date - 42;: -- rows older than 42 days

delete from local_table
where created_at_column &lt;= current_date - 42;


Where archive_table is the foreign table that "points"  to the Windows server.

The DELETE will probably be quite slow (the more rows you archive, the slower it will be), a faster way would be to partition the table e.g. by month and then detach old partitions from the table.