Questions tagged [archive]
The archive tag has no usage guidance.
116
questions
0
votes
1
answer
44
views
PostgreSQL: Best practises for Deleting huge volume of Data in Replicated Environment
We have a prod database (PostgreSQL13) with a hot-standby(physical replication) as well as logical replication to a different subscriber server. Now we are in requirement of purging few year's record ...
- 341
0
votes
1
answer
37
views
Manage the amount of data on tables
I can have several tables with 10+ millions rows in by year.
I would to know what's the best approach to maintain responsiveness with this amount of datas and I have two ideas.
Don't touch these ...
- 1
0
votes
0
answers
33
views
SQL Server database migration issues
I have a system with two servers and three client workstations. Servers were configured to work as principal/mirror servers (mirroring was configured with certificates). I have two databases in SQL ...
- 1
0
votes
0
answers
47
views
Archiving tables with foreign key constraints in PostgreSQL
I'd like to get your thoughts on the approach to archiving data from a set of tables with foreign key constraints. Please find the tables details below.
booking (Table):ID (UUID), created_date_time ...
1
vote
2
answers
6k
views
Best Practices to archive or remove old data in SQL Server 2012
Our application has numerous tables that contain outdated data, some dating as far back as 2010. Despite having indexes defined on these tables, many of the index seeks retrieve old records, which is ...
- 25
0
votes
2
answers
282
views
Pt-archiver csv double quote missing for some fields
I am trying to use pt-archive to archive my database. Below is my script for the archiving process.
pt-archiver --source h=localhost,D=***,t=** --user=*** --password=**** --where "dateTime<'...
- 227
1
vote
1
answer
107
views
data archival postgres
I have this code to delete rows with column "id". I want to limit the number of rows deleted in each loop. This code deletes the first 25k rows and the next loop deletes nothing since its ...
- 101
1
vote
0
answers
422
views
"Snapshot" of Historical Data for Analysis Purposes
I pull monthly aggregate data from a SQL database. I store the aggregated data in a separate table in the same database (so I can quickly grab numbers from six months ago, for instance).
My issue is ...
0
votes
1
answer
1k
views
Archiving SQLite DB with WAL mode
I have an SQLite database gapongasi.db configured using WAL mode and I want to have daily archive with tar(1). With WAL, there are two files being used (1) gapongasi.db-wal and (2)gapongasi.db-shm, ...
0
votes
1
answer
2k
views
understanding pg_wal and archiving
So I inherited this database. At the very beginning, there were already some files on pg_wal.
Then, after some weeks, I do count SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{...
- 317
0
votes
1
answer
95
views
Why is a Move and Delete Data (approx 1/3 of the table) using (CTEs) WITH statements taking hours on a 600m row table taking 3+ hours - best approach?
Am not sure if this is the best method of moving/deleting data. The play table has 700m+ rows and game table has over 600m+ rows.
I have 2 tables play and game, information from the play table is ...
- 459
1
vote
3
answers
1k
views
Database archiving strategy
I work on a payment system (a bit regulated area with requirements to store really old data for audits & customer requests).
We have SQL Server 2016 (but will move to 2019 soon).
System is 16 ...
- 161
1
vote
1
answer
665
views
Best way for archiving old mysql records
we have a Ruby on Rails app with a MySQL DB (on AWS RDS). Some of our tables have a large number of records ~100 million and this number is rapidly increasing every month. Most of the records in these ...
John Stevenson
0
votes
1
answer
378
views
Oracle - Big Table Archiving
Version: Oracle 12cR2 EE
The table contains the following column types: NUMBER, CLOB, NVARCHAR2, TIMESTAMP, VARCHAR2
Table size: 95GB
Table row count: 20M
Table partitioned? No
Indexes on table: there ...
- 29
-1
votes
1
answer
1k
views
Best practice for data archiving
I am trying to figure out the best practice for data archiving. I have 1 pair of Master/Slave MariaDB servers where data is replicated from Master to slave. Since this is a very old app I am not able ...
- 203
2
votes
1
answer
4k
views
Best practice for removing old data in postgres table but retaining a copy of removed data
We currently have a table that has approx 20 columns (one of the columns being a timestamptz datatype) in our database server which has 834 million rows. This in terms of size is a big table (approx ...
- 459
0
votes
1
answer
78
views
When should I Switch In from Stage Table to a SQL OLTP Partitioned Table
I have an OLTP Database with a big table (30M records a day / 400TPS). I want to implement Partitioning & Sliding Window on that table with Monthly Partitions plus a temp Stage-Table.
The time to ...
- 9
0
votes
1
answer
87
views
SQL Server Partitioning by Local And Remote database
We have a table related to documents(about 17m records on one table), and another one for properties of that documents.
And team decided to archive old entries. I thought about partitioning with ...
- 103
0
votes
1
answer
3k
views
Determining data retention period
I have an application that has generating data for the past 15 years. Currently, no retention period is configured so the data size grows day by day. I would like to add a retention period to the data ...
0
votes
1
answer
387
views
MYSQL 5.7 Partition a table based on partition key of another related table
Please understand a little background before getting to know the problem.
TABLE transaction (
t_id: AUTO INCREMENT BIGINT
t_execute_on_date: DATE,
timestamp: DATETIME,
... other columns
)
The ...
0
votes
1
answer
1k
views
I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table
I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table. Our inv table is huge btw lets use (x) as the number....
0
votes
1
answer
192
views
How to properly replicate and archive at once
I have a 300GB database that is growing around 5GB a day. It stores time-based data, and as such, I currently have around 60 days of data. My main database server has a 450 GB NVMe, so I want to get a ...
- 163
0
votes
2
answers
67
views
Analyze MySQL what needs to be archived
I'm not a DBA, I'm a DevOps guy trying to manage a MySQL database which is ever growing and desperately needs some archiving. My question is not how to archive. I think I have a pretty good idea and ...
0
votes
1
answer
512
views
Options for archiving old MySQL 5.7data
I have an web application that has a 1TB MySQL 5.7 production database.
We are running into performance issues due to table data size. We are planning to archive old data from different tables. ...
0
votes
1
answer
391
views
Table partitioning on OLTP
On SQL Server 2017 Std, I have an SSIS package that runs once a day on a 24×7 mission critical operational database to archive the largest transactional tables. The data is moved to tables on a ...
2
votes
1
answer
38
views
accessing, updating a large MYSQL table advice and guidance
I'm after some guidance or advice really. We've built an application and its gone mental with custom since coronavirus. We are after some advice for something we have never tackled.
The problem we ...
- 123
0
votes
1
answer
106
views
Yearly database archive design
I need to implement a DB of tracking numbers. The tracking numbers table could potentially contain billions of records during its lifetime so an archive database should be implemented to improve ...
- 169
0
votes
2
answers
950
views
Archive old data in a large table in DB2
I have a very large non-partitioned table with ~4,500,000,000 records: ODS.LARGE_TABLE.
The table has stored 3 months of data with ~50,000,000 rows per day, therefore resulting total of ~4,500,000,...
- 121
0
votes
0
answers
599
views
Archiving Huge table with Foreign Key dependencies
We have a huge table of ~ 1TB (data+Indexes) which has ~7 child tables with Foreign key relation ship
We have decided to archive the huge table but not the child tables(since they are not that big )
...
- 89
0
votes
1
answer
2k
views
MongoDB - Can I create TTL index with rolling method
Im getting stuck with archiving a huge amount of data in MongoDB 3.6
I want to delete 506 million records in a collection. I tried to remove using bulk.remove(), but that is also slow. 50 records are ...
- 1,950
6
votes
2
answers
4k
views
Managing a SQL Server database with over one terabyte of data [closed]
Recently we have an audit database that went over one terabyte and, since we have storage problems, management is looking for options.
My proposal is at the end of each year we take a backup and ...
- 1,063
0
votes
0
answers
706
views
Archiving a huge SQL Server table with change data capture and foreign key constraints on multiple columns
We are trying to archive a huge table with ~170 million rows, which is partitioned, has change data capture (CDC) enabled and has ~5 foreign key constraints.
We decided on the archival since the ...
- 89
0
votes
1
answer
229
views
Auto-archive MariaDB10 database table
I am trying to auto-archive active database table to archive database table using stored procedure and event in MariaDB10. These are the steps I am following:
Create new table everyday just before ...
- 1
2
votes
2
answers
1k
views
How to move old data to cheap hard drive
I have a quite big PostgreSQL database (with timescale plugin). Right now it is consuming about 500Gb on a SSD. Most of the data is in the form of time series. In most cases data older than a few ...
- 125
2
votes
1
answer
10k
views
archive command repeatly failing for one particular file
I am seeing many pg_log files full of following errors
2019-07-08 00:04:06 CEST DETAIL: The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/...
- 145
0
votes
1
answer
75
views
Which mechanism is best for previous data archived in oracle?
Our company has a big data server in which large data is stored, like images, video, audio, etc. Our company wants a process running with the new year, such that in 2020 the previous year's 2019 data ...
- 123
1
vote
2
answers
131
views
Resetting a new archiving database
I have an archiving question.
We have a History DB used for archiving old records from our main system and the application knows to query it
However, it becomes too big.
I would like to ...
- 575
0
votes
0
answers
133
views
Archiving SQL Server data on disk to be pottentially restored
Right now, we are backing up data per table into text/CSV files per month. This works fine for fairly small tables. For bigtables, the text files become so big that the restore process for a month ...
- 1,091
2
votes
1
answer
5k
views
mongodb - monthly archival of data
Coming from a RDBMS background, this is what I have done in the past.
Create a table with partitions based on a date field. There are typically three tables (partitions) - current (M0), previous ...
- 155
1
vote
0
answers
221
views
How to migrate InnoDB tables with 10s of partitions from the data directory onto another drive on the same server
I am trying to move some of the older partitions from larger tables located at the main data directory to another drive/mount point on the same server. I want all the partitions/tables to be ...
- 11
3
votes
3
answers
985
views
SQL Server replicating data to an archive
I have the following scenario:
Multiple SQL Servers where production machines log about 80GB of data (mostly images) daily in a database (each machine has a server with one database). Since these ...
- 131
0
votes
1
answer
47
views
Mysql Slave - Stop applying changes from a particular user
I have a mysql master and 3 slave. In the master im running Archive commands to delete old records everyday.
But I don't want to Purge the records on the Slave 3. Im using this as a Archive DB.
So,...
- 1,950
3
votes
1
answer
825
views
MongoDB sharding by monotonic id with zones for archiving
Situation:
I have an ever-growing collection of documents which do have monotonically increasing unique id.
All queries are direct lookup for documents with specified _id (no range queries nor ...
- 131
4
votes
2
answers
936
views
Should Archive Tables have their own Surrogate Identity Key
Should archive tables have their own surrogate identity id/key? For a customer sales table example:
We have original table, and then an additional archive table with surrogate archive id.
Rules ...
user155025
2
votes
2
answers
650
views
Retrieve archived data [closed]
MSSQL database has previously been archived on another database. Approximately 70m rows (includes sales information.)
Now, I am asked to move this archived data to live database. Because, customer ...
- 1,109
1
vote
2
answers
4k
views
SQL Server Archiving Data Functionality
Our company is looking for functionalilty/or automated way to archive data in SQL Server.
Currently, we create archive tables with identity surrogate, and develop stored procedures to import data. We ...
user129291
1
vote
0
answers
106
views
How to configure partial replication of Oracle DB
Suppose we have 2 identical Oracle 11g DB schemas: PRODUCTION and ARCHIVE.
The goal is to keep in PRODUCTION the minimal amount of data required for online operations,
and use ARCHIVE to store all the ...
- 131
3
votes
1
answer
2k
views
archive command is not working
One of our ancient PostgreSQL server's used disk space has increased to 90%. First I've checked the archive command:
archive_command = 'rsync -aq %p /data/wal-archive/%f'
The problem with that ...
- 63
18
votes
1
answer
19k
views
What is the quickest way to purge data?
Scenario:
We have two tables Tbl1 & Tbl2 on the Subscriber Server. The Tbl1 is being replicated from Publisher Server A and it has two triggers - insert and update. The triggers are inserting and ...
1
vote
1
answer
3k
views
What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction
We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on.
We are using partitioning for this and ...
