Does Migrating n8n from SQLite to PostgreSQL Require a Schema Export?

At Hostna, we operate a variety of automation environments where the transition from a file-based database to a relational database management system is a common milestone for scaling. When you migrate n8n from SQLite to PostgreSQL, you do not need to perform a manual schema export. The n8n application is designed to detect an empty database and automatically execute its internal migration scripts to create the necessary tables, indexes, and constraints upon the first successful connection. However, while the structure is handled by the application, the actual movement of your existing data—workflows, credentials, and execution history—requires a deliberate strategy involving either the n8n command-line interface or an external migration utility.

The Automatic Schema Mechanism

The n8n application uses TypeORM to manage its database interactions. When you change the DB_TYPE environment variable from sqlite to postgresdb, the application initializes a connection to the PostgreSQL host. If the target database is empty, n8n identifies that the current schema version is zero and proceeds to run every migration file stored in its source code. This ensures that the table structure in PostgreSQL exactly matches the requirements of the specific n8n version you are running. Because of this mechanism, a manual schema export (SQL file containing CREATE TABLE statements) is redundant and can sometimes cause conflicts if the versions do not align perfectly.

For those managing a n8n automation environment, the primary concern is not the structure, but the data. If you start n8n with a fresh PostgreSQL database, you will find a completely empty instance. To preserve your work, you must choose between migrating only the metadata (workflows and credentials) or the entire database state, including the execution history.

Method 1: Metadata Export via n8n CLI

If you do not require your past execution logs, the most reliable way to migrate is using the built-in n8n CLI tools. This method exports your workflows, credentials, and tags into JSON files, which can then be imported into the new PostgreSQL-backed instance. This approach is often preferred because it avoids the complexities of cross-database SQL conversion.

# Export all workflows
n8n export:workflow --all --output=/home/node/workflows.json

# Export all credentials
n8n export:credentials --all --output=/home/node/credentials.json

Once the files are generated, you update your environment variables to point to PostgreSQL and run the corresponding import commands. Note that the N8N_ENCRYPTION_KEY must remain identical across both instances, as credentials exported from SQLite are encrypted using this key. If the key changes, the new instance will be unable to decrypt the credential data, rendering your integrations broken. This is a critical step in maintaining a Linux server instance used for production automation.

Method 2: Full Migration with pgloader

When you need to retain every execution record, a simple JSON export is insufficient because the execution_entity table can contain thousands or millions of rows that are not captured by the export:workflow command. In this scenario, a tool like pgloader is required to transform the SQLite database engine file into a PostgreSQL-compatible format. pgloader handles the data type mapping between the two engines, such as converting SQLite’s loosely typed columns to PostgreSQL’s strict types.

A typical pgloader configuration for n8n might look like this:

load database
     from sqlite:///root/.n8n/database.sqlite
     into postgresql://user:password@localhost/n8n

 with include drop, create tables, create indexes, reset sequences

 set work_mem to '64MB', maintenance_work_mem to '256MB';

During this process, system stability is vital. A process running on a machine with 4 GB of RAM and a 2 GB memory_limit can still trigger the OOM killer if the kernel’s vm.swappiness is set too low and the application attempts to allocate a large contiguous block during a database dump. On our platform, we recommend monitoring memory usage closely during the pgloader execution to ensure the migration does not stall.

Database Connection Configuration

After the data is migrated, you must configure n8n to use the new database. This is handled entirely through environment variables. If you are using a virtual private server, you will likely define these in a .env file or within your Docker Compose configuration. The following variables are mandatory for a PostgreSQL setup:

• DB_TYPE=postgresdb
• DB_POSTGRESDB_HOST=localhost
• DB_POSTGRESDB_PORT=5432
• DB_POSTGRESDB_DATABASE=n8n
• DB_POSTGRESDB_USER=n8n_user
• DB_POSTGRESDB_PASSWORD=your_secure_password

To ensure secure communication, especially if the database is not on the same local network, you should enable SSL. Using a TLS 1.3 one-RTT handshake for the database connection reduces the initial connection latency compared to older protocols, which is beneficial for workflows that trigger frequent database writes. You can find detailed requirements for these variables in the n8n database migration guide provided by the developers.

Performance Considerations and Sizing

PostgreSQL offers significantly better performance for concurrent operations than SQLite, which uses file-level locking. However, PostgreSQL requires more careful resource management. For instance, the shared_buffers parameter in PostgreSQL should typically be set to roughly one quarter of the total system RAM to ensure efficient caching of the execution_entity indexes. If your n8n instance handles high volumes of data, the disk performance becomes the primary bottleneck.

PostgreSQL performance on our German-hosted stack is often bound by NVMe IOPS rather than raw throughput, especially during the execution_entity migration where thousands of small writes occur. If you notice a high TTFB on your n8n webhooks after the migration, it may be due to database contention or insufficient max_connections in the PostgreSQL configuration. You can check the current connection count with the following command:

psql -U n8n_user -d n8n -c "SELECT count(*) FROM pg_stat_activity;"

Verifying Data Integrity

Before decommissioning your SQLite file, verify that the migration was successful. Check the row counts in the major tables to ensure no data was lost during the transfer. This is a standard part of our security responsibility model when handling data transitions. You can compare the count of workflows in both databases to ensure parity.

In SQLite:

sqlite3 database.sqlite "SELECT count(*) FROM workflow_entity;"

In PostgreSQL:

psql -U n8n_user -d n8n -c "SELECT count(*) FROM workflow_entity;"

If the numbers match, proceed to check the credentials_entity and tag_entity tables. If you find discrepancies, it is often due to a failure in the pgloader cast rules or a constraint violation in PostgreSQL that SQLite ignored. For more complex troubleshooting, you may need to consult the technical documentation section for specific SQL error codes.

Addressing Migration Failures

If the migration fails, the most common cause is a permission issue or a network timeout. Ensure that the PostgreSQL service is fully initialized before n8n starts. In Docker Compose, this can be managed with a healthcheck and the depends_on condition. If you are operating a dedicated resource environment, ensure that the firewall allows traffic on port 5432 from the n8n container’s IP range.

If you encounter persistent connection issues within your Hostna account, check the firewall settings in the control panel to ensure the database port is accessible. For further assistance, please provide our technical support team with the full logs from your n8n container and the output of the pgloader summary table. This data allows us to identify whether the bottleneck is at the network layer or within the database engine itself, ensuring your data processing agreement standards are maintained through a successful migration.

This article was drafted with AI assistance from Hostna’s own research pipeline and published without individual review. Tell us if anything here is wrong.