Import and Export Databases: mongodump, pg_dump, mysqldump and More

Intro

0:00 Import and export, on every database. A table, a database or a whole instance in one file, made by the database's own program, and a file brought back, table by table.

Dumps by hand

0:15 Moving data usually means the right version of pg_dump, mysqldump or SqlPackage on the right machine, the flags of each program, looked up again each time, passwords typed on a command line, and a restore of the whole database when one table was wrong. With API Maker, every card of the Instance API page exports and imports, with the database's own program, installed for you, its options, each with its help, and an import compared with the database of now, table by table.

0:48 In this video : an export of MongoDB, step by step, a collection as JSON, PostgreSQL, MySQL, MariaDB, SQL Server and Oracle, an import, compared table by table, a copy into a new database, the jobs, and the programs, on Sandbox Settings.

An export, step by step

1:06 Instance API, mongodb, catalog. The three dots of the card of the databases : Export Database, Import into Database, and the jobs of the instance. The card of the instances has the same for a whole instance, and the card of the collections for the ones you select. Export Database. At the top, the program the jobs of this instance run : MongoDB Database Tools, here the one of the server.

1:35 What to export : the whole instance, this database, or some collections. The form of the file : the archive of mongodump, with the documents, the indexes, the options and the views. Below, the options of mongodump, each with its help behind the question mark. Gently runs the job at the least priority of the server : your APIs go first. Export. The job runs on the server, and the file is ready : its name says the instance, the database and the time, with its size and its SHA-256.

2:09 It stays on the server for a day by default, then only the job and its log stay. The log says what the program did : mongodump reads its password from a file of its own, never from the command line. Download : the browser downloads it from a link the server makes for this file, good for fifteen minutes. In the zip : the archive of mongodump, manifest.json, which describes the database and each collection, and README.txt, the commands which restore it by hand, without API Maker.

A collection as JSON

2:43 One collection works the same way. reviews, Export Collection. A collection can also go out as JSON or CSV, written by mongoexport. JSON, with a filter of MongoDB : the reviews of four stars and more. Export, and the reviews are in the file.

PostgreSQL

3:04 PostgreSQL. postgres, crm, Export Database. The PostgreSQL client, installed from its publisher. A dump of pg_dump, restored table by table and in parallel, or a script of SQL. Here, the support tickets keep their table, without their rows. Export, and the log says each step of pg_dump.

MySQL

3:28 MySQL. mysql, sales, orders, Export Table. The MySQL client of the line most compatible with this server, installed the first time. For one table, a condition for its rows : the delivered orders only. Export : a script of mysqldump, compressed.

MariaDB

3:51 MariaDB. mariadb, marketing, Export Database. MariaDB publishes no client for a Mac : the MySQL client works in its place, and the dialog says so. On most servers of Linux, it is mariadb-dump. Export, and the script is ready.

SQL Server

4:11 SQL Server. sqlserver, warehouse, Export Database. A bacpac of SqlPackage, the schema and the data, or a script of T-SQL written by the server itself : the tables, their rows and their foreign keys. Microsoft publishes SqlPackage for x86 servers : on an ARM server, the script. Export, done.

Oracle

4:37 Oracle. oracle, the schema HR, Export Schema. Data Pump runs inside the database itself : no program on the server. One moment : every table as it was when the export started. A dump an older Oracle reads, workers, and the directory object. Export, and the file of Data Pump comes back from the database.

Six engines

5:02 Six engines, each with its own program, its own forms of file, and its own options. Every file can be restored by hand with that program, without API Maker : nothing is locked in.

An import, table by table

5:16 Now an import. This morning, catalog was exported. Since then, three reviews were deleted by mistake, and a customer wrote a new one. The file of this morning has the three, but not the new one. Import into Database. The file of this morning : it is sent at once, in pieces, and read. What it holds : an export of the panel, from mongodb, made with mongodump, its size, its SHA-256, and its four collections.

5:49 Then, compared with the database of now : each collection of the file beside the collection of today. reviews changed : its documents differ. The others are the same. A table can also be new, or there now only. For each one, you choose. Replace : the table becomes the one of the file. Merge : the rows of the file which are not there are added. Keep, or Remove.

6:16 Nothing, then Merge for reviews : the three lost reviews come back, and the new one stays. Import. Yes. Then each table chosen is counted again : reviews has its three reviews back, and the new one.

A copy into a new database

6:33 To look at a file without touching anything, import it into a new database. The export of crm, chosen, into crm_review : a new database, made by the import. Every table is new : Everything takes them all. Import, yes, and crm_review sits beside crm.

Jobs and programs

6:56 Import & Export Jobs : every export and import of the instance, newest first, with its file and until when it stays. Two jobs run at a time on each server, the others wait for their turn. Tick some, Remove two, with their files, or Clear all. Sandbox Settings, Import & Export Tools : one row per program, with its publisher. The versions your account keeps on every server : here an older PostgreSQL client, for the older PostgreSQL of Calm Home.

7:28 The jobs run it, or Automatic : the version most compatible with each database, installed the first time a job needs it. This server says what it runs now, found by itself. And how long the files of the jobs stay.

Safe by default

7:45 The passwords of the instances never go on a command line : the programs read them from files only the server can read, removed when the job ends, and hidden in the logs. A download link opens for fifteen minutes. And an import which replaces or removes data asks for the name of the database first.

Recap

8:06 To sum up. Export and import from the three dots of every card. The database's own programs, installed for you. Six engines, each with its forms and its options. Imports compared with the database of now, table by table. The jobs, and the programs on Sandbox Settings. Next : the same exports, on schedules, into S3. API Maker. Build faster, spend smarter, deliver better.