What's the fastest way to copy a collection within the same database?

Question

I want to copy a collection within the same database and give it a different name - basically take a snapshot.

What's the best way to do this? Is there a command, or do I have to copy each record in turn?

I'm aware of the cloneCollection command, but it seems to be for copying to another server only.

I'm also aware of mongoimport and mongoexport, but as I'm doing this via PHP I'd prefer not to make calls out to the shell.

`db.myoriginal.aggregate([ { $match: {} }, { $out: "mycopy" } ])` This is the faster than accepted answer. — Milan, Mar 10 '21 at 10:59
Good to know, thanks. I am unable to unaccept the answer, but it may have been correct in 2012. $out requires v2.6 of MongoDB which was not released until 2014. — Tim, Mar 10 '21 at 14:30

score 313 · Answer 1 · edited Apr 05 '22 at 16:01

313

> db.myoriginal.aggregate([{$out: "mycopy"}])

It is a lot faster than doing many inserts in a forEach loop.

edited Apr 05 '22 at 16:01

Victor Schröder

6,738
2
42
45

answered Nov 24 '15 at 08:55

jschildgen

3,678
4
16
13

I just tried this, it's very fast. You need to include a match operator in there too: db.stream.aggregate([{$match: {}},{$out: "s2"}]); On my powerbook it took 2 seconds to copy 50,000 documents each a few KB – Ed Sykes Dec 02 '15 at 11:36
11

12GB of data in 1-2 minutes on a i5 PC. And the best part : it's non-blocking – Stefan Rogin Jan 28 '16 at 08:45
1

pls notice that target can't be a *capped collection*. – Vitaly Dyatlov Aug 18 '16 at 15:50
9

Thanks, that's perfect. Be aware that the indices will be missing – kraf Mar 16 '17 at 18:20
Doesn't work for versions older than 2.6 (my case). The first answer worked for me. – chrmcpn Jul 31 '17 at 20:27
Is there a way to ignore duplicate key error with this approach? I'm trying to duplicate a large collection and apply new unique compound key. Any ideas? – zub0r Oct 17 '17 at 11:11
5

How can I include the index to the new collection with this method? – aandis Dec 06 '17 at 04:22
1

@aandis From what I know, you will need to add the indices in a subsequent step if you were to follow this method. This would help - https://stackoverflow.com/questions/43150231/mongodb-how-to-copy-index-definitions-from-one-collection-to-another – Analytical Monk Jan 15 '18 at 12:20
Not sure why, but I needed to add a `.to_a` or `.count` at the end to get this to work. – Gundam Meister Aug 28 '19 at 20:48
4

Why to insert { $match: {} }?. Actually it works without it for me (I have 4.2). – Alisettar Huseynli Oct 19 '19 at 22:24
@StefanRogin how much RAM do you have? For me, memory usage increase is ~5 times greater than the collection being copied. – Никита Михайлов Mar 11 '20 at 13:20
@НикитаМихайлов I had 16 or 32 GB – Stefan Rogin May 04 '20 at 11:56
1

That said I don't remember at the time any issues with memory. – Stefan Rogin May 04 '20 at 12:31
1

4.5 million documents copied with in 40 secs. – Muhammad Faizan Fareed Jan 25 '21 at 10:13
This is the best solution IMHO, fast and simple, thanks for sharing! – nnimis Aug 09 '21 at 18:57
Cannot copy to sharded collection – gzc Apr 15 '22 at 10:18
I don't understand, I am using this approach on a table with `STORAGE SIZE: 912.98MB`, `LOGICAL DATA SIZE: 3.35GB`, `TOTAL DOCUMENTS: 8535855`, `INDEXES TOTAL SIZE: 332.62MB` in MongoDB Atlas and it takes 5 minutes. Meanwhile, others in this thread are copying 12GB of data in 1-2 minutes, 5 years ago? – Canovice Nov 03 '22 at 13:54

score 69 · Accepted Answer · edited Jan 15 '20 at 16:56

69

You have a few options, but the fastest is:

mongodump -d db -c sourcecollection 
mongorestore -d db -c targetcollection --dir=dump/<db>/<sourcecollection.bson>

or

mongoexport -d db -c sourcecollection | mongoimport -d db -c targetcollection --drop

or in php:

`mongoexport -d db -c sourcecollection | mongoimport -d db -c targetcollection --drop`;

after that you have

mongo db < script.js

where, as shown in the mongo docs, script.js contains something like:

db.myoriginal.find().forEach( function(x){db.mycopy.insert(x)} );

The slowest (by an order of magnitude or more) way to copy a collection will be to use the native php driver - simply because of moving information around. But you could issue the above mongo query if you absolutely want to avoid cli calls using the db execute function.

edited Jan 15 '20 at 16:56

carla

1,970
1
31
44

answered May 16 '12 at 21:36

AD7six

63,116
12
91
123

Thanks. I am currently using your first option, but was hoping there was a command I could simply call so I don't need to call the shell from PHP. – Tim May 17 '12 at 13:04
I may try the script approach using http://php.net/manual/en/mongodb.execute.php – Tim May 17 '12 at 13:06
1

Using a dumb mapReduce (self-mapping, no reducing), measures between mongoexpor and script.js solutions for me... – Vajk Hermecz Jun 25 '13 at 23:39
1

Mongodump | mongorestore should be faster than export|import since it doesn't have to deserialize the binary format to json or csv. – Cody A. Ray Feb 05 '17 at 15:46
@CodyA.Ray it's a long time since I've used mongo, this answer was provided based on observed performance at the time (now 4+ years ago). Please update the answer if you are able to demonstrate a better method. – AD7six Feb 05 '17 at 16:56
1

How to preserve indexes during copying to targetcollection? – abdulmanov.ilmir Mar 01 '17 at 07:21

AbdelHady · Answer 3 · 2022-04-15T15:14:56.993

17

For versions less than 3.0, you can use copyTo() for experimental uses (never use on Production, as it blocks write operations):

db.source.copyTo("target");

& if "target" doesn't exist, it will be created

edited Apr 15 '22 at 15:14

answered Jan 19 '14 at 17:42

AbdelHady

9,334
8
56
83

4

Upto v2.6.3 `copyTo()` will block all operations on the `mongod` instance. From documentation: > Because copyTo() uses eval internally, the copy operations will block all other operations on the mongod instance. – Yogesh Mangaj Jun 23 '14 at 14:27
1

@YogeshMangaj thanks for the note, I updated the answer to be easily visible for everybody. – AbdelHady Dec 16 '14 at 11:16
9

Deprecated since version 3.0. – Francesco Casula Sep 30 '15 at 15:00
DON'T DO THIS. As explained in the docs, this will block all write operations. – lbolla Sep 24 '18 at 10:11
1

@lbolla I already mentioned this in the answer itself months ago :D – AbdelHady Sep 24 '18 at 10:42
In addition to blocking and being deprecated It's also insanely slow – Adrien Rey-Jarthon Aug 09 '19 at 11:58
then whats the modern way to copy if this is deprecated? – theprogrammer Jan 15 '20 at 03:26
@theprogrammer, the best way is the other answer posted here (just above my answer) by yoooshi, which uses aggregation. – AbdelHady Jan 15 '20 at 12:10

score 4 · Answer 4 · edited Apr 05 '22 at 15:49

4

First option: using mongodump

Get a dump from source_collection

$ mongodump -d db -c source_collection

Restore to target_collection collection from the dump

$ mongorestore -d db -c target_collection dir=dump/db_name/source_collection.bson

Second option: $out aggregation step

Run an aggregate command on source_collection

> db.source_collection.aggregate([
    {$match: {emailAddress: "apitester@mailinator.com"}},
    {$out: "target_collection"}
  ])

Third option (slowest): iterate and copy all documents

Run a loop through all documents of source_collection, inserting them into target_collection

> db.source_collection.find().forEach((doc) => {
    db.target_collection.insert(doc);
  })
  && print("Copy completed!");

edited Apr 05 '22 at 15:49

Victor Schröder

6,738
2
42
45

answered Feb 10 '20 at 09:29

Isura Amarasinghe

852
8
13

1

Which one of the first two options is faster? – pooya13 Jun 24 '21 at 17:17
1

The second option is the fastest, because everything happens inside MongoDB and the data never leaves the server. You don't need local disk space and won't use the network for data. The downside, it won't preserve indices. The first option is decent, but still requires the whole data to be transferred outside the server and then transmitted back. You have to account for disk space and bandwidth. The third option is very slow because it processes inserts individually and transmits each document over the network. It doesn't require local disk space, though. – Victor Schröder Apr 05 '22 at 15:54
Agreed with @vi – Isura Amarasinghe Apr 24 '22 at 11:23

score 1 · Answer 5 · answered May 04 '23 at 02:07

Sorry for the answer, but I don't have enough rep to post a comment.

The accepted answer has a typo, it should be:

$ mongorestore -d db -c target_collection --dir=dump/db_name/source_collection.bson

note the -- before dir that isn't present in the accepted answer.

score 0 · Answer 6 · answered May 08 '13 at 11:28

0

In addition to AD7six 1st solution, if you use mongoexport / import be sure about your collection data types and mongo configuration, as explained here: http://docs.mongodb.org/manual/reference/mongodb-extended-json/

answered May 08 '13 at 11:28

kij

1,421
1
16
40

1

Please clarify/state what you wish to point out. Mongoimport and mongoexport use the same format, there's nothing to configure, and no risk of data mutating afaik. – AD7six Jun 26 '13 at 05:49

score 0 · Answer 7 · answered Apr 24 '19 at 13:34

This is my implementation in python (pymongo):

def copy_collection(client, from_db, from_coll, to_db=None, to_coll=None):
    to_db = from_db if to_db is None else to_db
    to_coll = from_coll if to_coll is None else to_coll
    assert (to_db != from_db or to_coll != from_coll), "Copy Error: Source and destination can't be same!"
    documents = client[from_db][from_coll].find()
    client[to_db][to_coll].insert_many([d for d in documents])

Subhash Rawat · Answer 8 · 2022-04-06T11:12:59.057

I managed to do that with this query :-

Suppose you have collection name data1 and you want to make new collection data2 with same indexes or want to delete or add some index you can do this by using this query.

Query It took very less time in my case (speed depend on amount of data)

var cursor = db.data1.find();
var data = [];
while(cursor.hasNest()){
    var a = cursor.next();
    data.push({
        "FirstName" : a.FirstName,
        "MiddleName" : a.MiddleName,
        "LastName" : a.LastName,
        "Age" : a.Age,
        "Occupation" : a.Occupation,
        "Date" : new Date()
    });
}

db.data2.insertMany(data)

----- UPDATE -----

As this will build in memory array so for large set of data memory consumption will be high, so to solve this problem what one can do is define a flag and after some record let say 1000 you can insert the current data in collection and empty that array.

Yes, it saves time by accumulating documents and inserting in bulk, but this will create a brand new collection, without any indices. Also, for large collections, building an in-memory array with the documents may be too much memory footprint for the machine operating the MongoDB shell. — Victor Schröder, Apr 05 '22 at 15:58

score -1 · Answer 9 · answered Jan 12 '18 at 12:27

-1

The fastest way is db.collection.copyTo().

Note that it is deprecated since version 3.0.

answered Jan 12 '18 at 12:27

chris

2,761
17
24

score -3 · Answer 10 · answered Nov 10 '13 at 09:32

-3

You can use the copyDatabase function in the mongo shell:

http://docs.mongodb.org/manual/tutorial/copy-databases-between-instances/

answered Nov 10 '13 at 09:32

Jp Lorandi

113
1
3

4

Note that [link-only answers are discouraged](http://meta.stackoverflow.com/tags/link-only-answers/info), SO answers should be the end-point of a search for a solution (vs. yet another stopover of references, which tend to get stale over time). Please consider adding a stand-alone synopsis here, keeping the link as a reference. – kleopatra Nov 10 '13 at 09:41

What's the fastest way to copy a collection within the same database?

10 Answers10

Linked

Related