As the first step, you need to set up a machine for Database Lab Engine instance. See the guide Set up a machine for the Database Lab Engine.
In order to set up Database Lab Engine to automatically get the data from database using dump/restore you need to use following jobs:
Copy the contents of configuration example
config.example.logical_generic.yml from the Database Lab repository to
~/.dblab/engine/configs/server.yml and update the following options:
- Set secure
server:verificationToken, it will be used to authorize API requests to the Engine
- Set connection options in
dbname: database name to connect to
host: database server host
port: database server port
username: database user name
password: database master password (can be also set as
PGPASSWORDenvironment variable of the Docker container)
- Set proper version in Postgres Docker image tag (change the images itself only if you know what are you doing):
sudo docker run \ --name dblab_server \ --label dblab_control \ --privileged \ --publish 2345:2345 \ --volume /var/run/docker.sock:/var/run/docker.sock \ --volume /var/lib/dblab:/var/lib/dblab/:rshared \ --volume /var/lib/dblab/dblab_pool/dump:/var/lib/dblab/dblab_pool/dump \ --volume ~/.dblab/engine/configs:/home/dblab/configs:ro \ --volume ~/.dblab/engine/meta:/home/dblab/meta \ --volume /sys/kernel/debug:/sys/kernel/debug:rw \ --volume /lib/modules:/lib/modules:ro \ --volume /proc:/host_proc:ro \ --env DOCKER_API_VERSION=1.39 \ --detach \ --restart on-failure \ postgresai/dblab-server:2.5.0
You can use PGPASSWORD env to set the password.
# Stop and remove the Database Lab Engine control container.sudo docker rm -f dblab_server # Clean up data directory.sudo rm -rf /var/lib/dblab/dblab_pool/data/* # Remove dump directory.sudo umount /var/lib/dblab/dblab_pool/dumpsudo rm -rf /var/lib/dblab/dblab_pool/dump
A basic way of restoring database from the source contains three steps:
logicalDumpwhere DLE dumps from the source into files
logicalRestorewhere downloaded dumps are restored into the DLE instance
logicalSnapshotwhere a snapshot is taken
Since dump files are stored in intermediate files, make sure there is enough disk space.
A typical configuration might look like this:
retrieval: jobs: - logicalDump - logicalRestore - logicalSnapshot spec: logicalDump: options: dumpLocation: "/var/lib/dblab/dblab_pool/dump" dockerImage: "postgresai/extended-postgres:13" source: type: remote connection: dbname: postgres host: 220.127.116.11 port: 5432 username: postgres password: postgres logicalRestore: options: dumpLocation: "/var/lib/dblab/dblab_pool/dump" dockerImage: "postgresai/extended-postgres:13" logicalSnapshot:
To restore from existing dumps, describe two jobs
logicalRestore job provide with the
dumpLocation option a dump location where files are stored.
dumpLocation option must provide a file or directory that contains dump files of various formats: plain, custom, directory.
You can specify both a separate file and directory containing dumps to restore. Please note that DLE will skip dumps of unknown format.
DLE supports consecutive (but single-threaded) restoring multiple dumps. You even can mix dumps of different formats in a
retrieval: jobs: - logicalRestore - logicalSnapshot spec: logicalRestore: options: # The location of the archive files (or directories, for directory-format archives) # on host machine to be restored. dumpLocation: "/var/lib/dblab/dblab_pool/dump" dockerImage: "postgresai/extended-postgres:13" logicalSnapshot:
Since DLE has to explore the
dumpLocation directory and parse objects (files and directories) inside it, you must mount the directory from
dumpLocation to the running Database Lab Engine container.
Database Lab Engine supports restoring from a plain-text file (using the
There are a number of possible scenarios of how a dump might be created:
DLE supports all derived dump files of the described types, such as those generated by
Database Lab Engine automatically detects plain-text dump files and their origin type.
If DLE is working with a dump made by
pg_dump with the
--create option, it doesn't need to know all database names from this file because psql runs queries and restores the dump to a correct database (even if the database already exists, even if the name is
and extracts database names as well.
If a provided dump has been made without the
--create option (or there are no tables, or the original type cannot be detected because of compression), then DLE will use the filename as a database name, adjust it (if necessary, see a note about a fallback naming below), and will try to create a new database and restore the dump into it.
Fallback naming. All characters in the file name other than words (
[^0-9A-Za-z_]) will be replaced with an underscore (
- partial restore is not available for a plain-text dump. You cannot restore only specific tables.
databases.tablesoption is not supported.
- parallel restore is not available for a plain-text dump. It is always single-threaded.
parallelJobsoption is not supported.
It is a great idea to compress dump files of large databases. Database Lab Engine supports restoring compressed plain-text dumps.
DLE supports several compression options for plain-text dumps:
This means that you can specify the
dumpLocation parameters pointing not only to raw but also compressed plain-text dumps.
DLE provides a way of restoring from the source on the fly. It's useful to dump and restore a database without saving an intermediate file - so called
The advantage of this method is that no additional disk space is required to restore the database.
Keep in mind that unlike a classic "logicalRestore", this option does not support parallelization (specify
parallelJobs: 1 for logicalDump job).
It is always a single-threaded (both for dumping on the source, and restoring on the destination end).
To restore directly, you do not need to use "logicalRestore" job. Just define a
logicalDump job and uncomment the
immediateRestore section inside it.
retrieval: jobs: - logicalDump - logicalSnapshot spec: logicalDump: options: dumpLocation: "/var/lib/dblab/dblab_pool/dump" dockerImage: "postgresai/extended-postgres:13" source: type: remote connection: dbname: postgres host: 18.104.22.168 port: 5432 username: postgres password: postgres parallelJobs: 1 immediateRestore: enabled: true logicalSnapshot:
By default, DLE dumps and restores all available databases. To manage list of databases you may option (
databases). Add this option to
logicalRestore jobs to specify a list of databases that must be copied.
Do not specify this option to take all databases.
To dump multiple databases, add a
databases section to the existing
logicalDump job listing the databases to be copied. For instance:
spec: # Dumps PostgreSQL database from provided source. logicalDump: options: ... databases: database1: database2: databaseN:
You could dump database partially by providing the list of tables to be dumped:
spec: logicalDump: options: ... databases: database1: tables: - table - table2 - tableN database2: databaseN:
Or do not add
tables section to dump all tables
To restore multiple databases, add a
databases section to the existing
logicalRestore job listing the databases to be restored. For instance:
spec: logicalRestore: options: ... databases: database1: database2: databaseN:
Or do not add
databases section to restore all databases
You could specify a non-default format of dumps (both: files and directories).
Supported dump formats:
By default, the logical restore job uses a
directory dump format. The Database Lab Engine will extract the database name from dump files.
You could restore database partially by providing the list of tables to be restored:
spec: logicalRestore: options: ... databases: database1: format: directory tables: - table - table2 - tableN database2: databaseN:
Or do not specify the
tables section to restore all available tables.