Syncing databases with PostgreSQL and meilisync
Though Meilisearch is a database, it is not recommended you use it as your primary data store. Instead, you should use an external database to store your data and periodically synchronize it with Meilisearch.
This guide teaches you to use
meilisync to keep Meilisearch up to date with a PostgreSQL database.
- A command-line console, such as macOS's terminal or Window's Cygwin
- A PostgreSQL database populated with data
- A Meilisearch instance, either self-hosted or via Meilisearch Cloud
meilisync to the same location as your primary database. You can do this manually or with Docker:
First, create a
docker-compose.yml file in your Docker project directory. Open it with a text editor and add the following content:
services: meilisync: platform: linux/x86_64 image: long2ice/meilisync volumes: - ./config.yml:/meilisync/config.yml
If your project already has a
docker-compose.yml, add the
meilisync settings to the existing
When you are done, open your console, navigate to your Docker project directory, and run the following command:
After a few seconds, Docker will inform you it has successfully pulled
meilisync to Meilisearch
meilisync installed, you now need to connect it to your Meilisearch instance.
config.yml file in your project's directory. Open it with a text editor and add the following configuration options depending on whether you are using Docker or not:
meilisearch: api_url: http://host.docker.internal:7700/ api_key: 'MEILISEARCH_API_KEY' insert_size: 1000 insert_interval: 10
MEILISEARCH_API_KEY with an API key able to create, update, and delete documents and indexes. Replace
MEILISEARCH_API_URL with your instance's API URL. If you are using Meilisearch Cloud, this URL should look similar to this: https://ms-4d85L33tC0d3-5041.fra.meilisearch.io
meilisync to Meilisearch, you need to connect it to your PostgreSQL database.
Enable logical replication
meilisync with PostgreSQL, you need to enable the
wal2json extension requires the Write-Ahead Log settings to be set to
This can be done by adding the following line to your PostgreSQL server's
wal_level = logical # # these parameters only need to set in versions 9.4, 9.5 and 9.6 # default values are ok in version 10 or later # max_replication_slots = 10 max_wal_senders = 10
Alternatively, set the binary log format during runtime by running the following command in your PostgreSQL server console:
ALTER SYSTEM SET wal_level = logical;
This will require a server configuration reload to take effect. Read more about this in the
ALTER SYSTEM documentation.
meilisync and PostgreSQL
config.yml once again and append the following settings to the bottom of the file:
source: type: postgres host: 127.0.0.1 # assuming your PostgreSQL server is running on the same machine as `meilisync` port: 5432 database: POSTGRESQL_DATABASE_NAME user: POSTGRESQL_USERNAME password: POSTGRESQL_PASSWORD
Replace POSTGRESQL_DATABASE_NAME, POSTGRESQL_USERNAME, and POSTGRESQL_PASSWORD with the name of your database, a user with read access to that database, and its credentials.
The last configuration step is specifying which tables in your database should be synced to which Meilisearch indexes.
config.yml and append the following settings to the bottom of the file:
sync: - table: POSTGRES_TABLE_NAME_1 index: MEILISEARCH_INDEX_NAME_1 pk: id # Read the "Primary key inference" section below full: true - table: POSTGRES_TABLE_NAME_2 index: MEILISEARCH_INDEX_NAME_2 full: true
MEILISEARCH_INDEX_NAME_1 with the names of your tables and indexes. As the above example demonstrates, you may sync multiple tables.
Primary key inference
If your table contains multiple columns with
id in their name,
meilisync will not be able to infer which column is the primary key. In this case, you must specify the primary key manually by adding a
pk field to the table's configuration.
This tutorial describes a fairly basic
meilisync setup. For more
customization options, consult
With the configuration done, open your command-line prompt once again and run
meilisync with Docker or your manual install:
meilisync should immediately start syncing your primary PostgreSQL database with your Meilisearch instance.
meilisync is done, use Meilisearch's search preview to perform a few test searches and confirm data has been successfully synced between your primary database and Meilisearch.
Congratulations—you have successfully set up
You may leave the process running permanently on the background so all changes to your primary database are automatically synced, or you may interrupt the process and only run it when you update your database again.