Questions tagged [archive]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
34 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 ...
0
votes
1answer
20 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 ...
0
votes
2answers
48 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 ...
1
vote
1answer
49 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/...
0
votes
1answer
32 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 ...
1
vote
2answers
64 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 ...
0
votes
0answers
58 views

how to backup, remove and then restore a filegroup of a partitioned table

Continuation of the question "Archiving SQL Server data on disk to be pottentially restored". I'm still looking for a way to archive data per month which can easily be restored... I'm in the ...
0
votes
0answers
39 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 ...
0
votes
1answer
340 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 ...
1
vote
0answers
38 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 ...
3
votes
3answers
267 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 ...
0
votes
1answer
29 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,...
2
votes
1answer
267 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 ...
3
votes
2answers
269 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 ...
2
votes
2answers
79 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 ...
0
votes
2answers
251 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 ...
1
vote
0answers
45 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 ...
2
votes
1answer
824 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 ...
18
votes
1answer
6k 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
1answer
771 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 ...
0
votes
1answer
637 views

Postgresql - archive_command Windows, not working

I'm attempting to set up archiving on a windows server machine but when I check the logs nothing is happening. The postgresql.conf file this is my input: #wal_level = replica #archive_mode = on #...
1
vote
1answer
440 views

mysql - How to repair & backup archive table, also stop growing .ARN extension file?

I have an MySQL table build with archive storage engine. The table has over 100k records. I have got disk full and it turned out to be a large .ARN file from an archive table. When I cleared some ...
1
vote
2answers
1k views

SQL Partition Function without actual values [duplicate]

I would like to partition a column by fiscalyear. How Do I create a partition function without harding coding values? This is what I currently have below. create table dbo.CustomerAccount ( ...
0
votes
1answer
196 views

Archiving strategy

I have an instance with only one database which contains 4 years of data (400 gigas). I name this instance inst_live I want to implement this following archiving strategy: create an archive instance (...
1
vote
1answer
331 views

DELETE … IN hangs after deleting half of a large table (even for deleting 10 rows)

I'm having trouble with a DELETE hanging, even for a small number of rows (10) in a large MariaDB InnoDB table. Background I had a large table (~400,000,000 rows) that needed to have rows archived ...
0
votes
1answer
1k views

Copy a mongo db collection without locking whole mongod instance

I am trying to run mongodb copy collection on same server. Requirement is we delete database as per our archival policy but we need to keep one collection from this data as a backup in separate db on ...
0
votes
1answer
485 views

Restoring dump(backup): ERROR 1022 (23000) at line 44: Can't write; duplicate key in table

I'm doing tests to change a current table from my InnoDB database to Archive, but I had a problem trying to restore a dump after the change. After several tests, the problem is related to the value ...
0
votes
1answer
2k views

MySQL - Delete older Records from a huge table

I have some huge tables which have around 10years data, and each table has approx 10000000+ rows. Basically, I want to remove older than 2 years data and make a copy of deleted data in a separate ...
2
votes
1answer
655 views

Archiving data in SQL Server with stored procedure [closed]

Database in SQL Server 2014 has one table and is consuming around 17-20 GB of disk space each day. Assuming that level of usage remains fairly consistent and not accounting for lower usage on ...
0
votes
1answer
895 views

Recovery using Archive redo log files

As I'm new in using RMAN and archivelog, I've a few doubts regarding to this. My database is Oracle 11g(windows platform). It was previously in noarchivelog mode and recently I've changed it to ...
0
votes
1answer
27 views

Help with updating and archiving data

I'm building a MySQL database and a frontend. One function of the database application is to make an invoice. I'm looking for a way to archive all of the data that went onto the invoice so that if ...
1
vote
1answer
498 views

Best practice for storing large files (+1GB each) [closed]

I am looking to build a big data platform for weather forecasting. I will be using a numerical model known as WRF which takes in GFS files and produces files which can be over 1GB each. I will be ...
0
votes
0answers
59 views

What index type to use for a log

I have a table in which I will store messages (as in chat messages). The table will be appended to a LOT, rows being inserted as messages fly by, much more often than entries will be updated. Older ...
2
votes
1answer
153 views

Archivelog redundancy

I need to enable archivelog. But sometimes database can't connect archivelog destination (NFS disk) and shutdowns. I can set two archivelog destinations and one of them is not NFS disk (local disk). ...
0
votes
1answer
60 views

Archiving tables with Access frontend and linked SQL Server

I'm working on a project where the user has an Access 2013 MDB database with forms, reports, and queries. We're converting it to use linked tables to SQL Server 2008 R2. All the linked tables seem to ...
2
votes
1answer
508 views

Truncate text output of COPY command for postgres

I've got a script that copies every table in a schema to a csv using the following: psql -Atc "select tablename from pg_tables where schemaname='$schema'" --host=$host -w --user=$user $database |\ ...
1
vote
3answers
2k views

Specify cleanup by file age - PostgreSQL 9.2

I want to keep my wal_files for 30 days, using the pg_archivecleanup. I've followed these scripts: https://stackoverflow.com/questions/31346606/postgresql-automatic-archive-purge-not-occuring and ...
2
votes
1answer
63 views

“symlink” data to new database

I am just trying to figure some ways to do manage archiving of some/most of our Application data within the database and wondering if something like this would be possible: Archive anything with a ...
23
votes
3answers
5k views

What's the best way to archive all but current year and partition the table at the same time

Task Archive off all but a rolling 13 month period from a group of large tables. The archived data must be stored in another database. The database is in simple recovery mode The tables are 50 mil ...
0
votes
1answer
59 views

Redo/Noredo in Oracle

I have an Oracle 11.2.0.3 about 700GB in size running in noarchivelog mode in a pretty slow storage setup. It barely gives IOPS and there is resource crunch in terms of hardware resources. The ...
1
vote
1answer
958 views

DB2 archive log extraordinary growth for any operation after runstat/reorg [closed]

My environment: DB2 10.1.4 Enterprise, IBM Security Identity Manager 6.0 FP6, AIX 7.1 Each archive log have a size of 40MB Yesterday, we do a maintainance on ISIM database by doing online backup. The ...
0
votes
1answer
119 views

Use a SP to Archive/Revision related records in several related tables?

I have a database for an application that has a complicated relational table structure. Is there a good way to create revisions of the data, or archive the data before a user makes changes? Ideally ...
2
votes
0answers
217 views

SQL Server archiving

We are trying to archive our data for the first time in our database. The database is around 250 GB. Our approach is as follows. Create a copy of those tables without any index or constraints. And ...
2
votes
0answers
447 views

Any way to ensure that Change Data Capture clean up process does not delete the change data that you haven't processed yet?

I'm using CDC feature to track changes that are being made, since the cleanup period is set to 3 days by default, I have to run jobs to archive this change data from the CDC system tables to my tables....
2
votes
1answer
697 views

postgres: WAL files not being deleted during PITR

I have got an issue with setting up PITR on my postgres server: WAL archiving and restoring works quite well, as well as restoring to a specific point in time. The issue however is, that if i do a ...
5
votes
1answer
230 views

Postgres: Is migrating old records to separate table and creating new aggregated records a good strategy to improve search speed?

I have a table with 1M+ records. New records are created and updated each day each time an event occurs for each type of event (which there are many). I frequently need to find sums across many ...
0
votes
1answer
1k views

Adding columns & index to a huge table

I'm working with a Rails app backed by a MySQL database. One of the more-frequently used models is backed by a table with over 200,000,000 rows (and growing). We've been aware for a while that the way ...
4
votes
1answer
692 views

Moving Transaction Data To Another Database For Reporting Purpose

We got a requirement from our developer group, to do the followings: Frequently (every 30mins) move the transaction data out of live database to another database Secondary database will be utilize ...
2
votes
2answers
899 views

How to archive a whole database?

I have a database that is not going to be used anymore but that needs to be kept around for a long time. I'd like to remove it from the production system. My goal is to save storage space when ...
0
votes
3answers
337 views

Schema Design with “Seasonal” Data

I am developing a SaaS web application for School Management. What I need help is this: The whole application is purposed for the current year that the School is in. However, some data and ...