starred/nginx-proxy-manager-NginxProxyManager

Fork 0

mirror of https://github.com/NginxProxyManager/nginx-proxy-manager.git synced 2026-04-25 09:25:55 +03:00

[GH-ISSUE #4265] [QUESTION] Postgres migration #2750

New issue

Open

opened 2026-02-26 07:36:40 +03:00 by kerem · 6 comments

kerem commented

2026-02-26 07:36:40 +03:00

Owner

Originally created by @dasunsrule32 on GitHub (Dec 29, 2024).
Original GitHub issue: https://github.com/NginxProxyManager/nginx-proxy-manager/issues/4265

Hello, I see that Postgress support was added with https://github.com/NginxProxyManager/nginx-proxy-manager/pull/4254. I am wondering if there is a migration path from the default sqlite to postgres? Thanks. :)

Originally created by @dasunsrule32 on GitHub (Dec 29, 2024). Original GitHub issue: https://github.com/NginxProxyManager/nginx-proxy-manager/issues/4265 Hello, I see that Postgress support was added with https://github.com/NginxProxyManager/nginx-proxy-manager/pull/4254. I am wondering if there is a migration path from the default `sqlite` to `postgres`? Thanks. :)

kerem added the

stale

label

2026-02-26 07:36:40 +03:00

kerem commented

2026-02-26 07:36:41 +03:00

Author

Owner

@dasunsrule32 commented on GitHub (Dec 29, 2024):

I tried a straight pgloader move and it failed.

pgloader -vvv sqlite://database.sqlite pgsql://npm:password@192.168.5.3/npm

Only failure is:

2024-12-29T21:33:51.167006Z ERROR Database error 22008: date/time field value out of range: "1712521072575-01-01"
CONTEXT: COPY migrations, line 1, column migration_time: "1712521072575-01-01"

Following errors in NPM:

[12/29/2024] [4:39:28 PM] [Global   ] › ℹ  info      Using Postgres configuration
[12/29/2024] [4:39:29 PM] [Migrate  ] › ℹ  info      Current database version: none
[12/29/2024] [4:39:29 PM] [Migrate  ] › ℹ  info      [initial-schema] Migrating Up...
migration file "20180618015850_initial.js" failed
migration failed with error: create table "auth" ("id" serial primary key, "created_on" timestamptz not null, "modified_on" timestamptz not null, "user_id" integer not null, "type" varchar(30) not null, "secret" varchar(255) not null, "meta" json not null, "is_deleted" integer not null default '0') - relation "auth" already exists
[12/29/2024] [4:39:29 PM] [Global   ] › ✖  error     create table "auth" ("id" serial primary key, "created_on" timestamptz not null, "modified_on" timestamptz not null, "user_id" integer not null, "type" varchar(30) not null, "secret" varchar(255) not null, "meta" json not null, "is_deleted" integer not null default '0') - relation "auth" already exists error: create table "auth" ("id" serial primary key, "created_on" timestamptz not null, "modified_on" timestamptz not null, "user_id" integer not null, "type" varchar(30) not null, "secret" varchar(255) not null, "meta" json not null, "is_deleted" integer not null default '0') - relation "auth" already exists
    at Parser.parseErrorMessage (/app/node_modules/pg-protocol/dist/parser.js:283:98)
    at Parser.handlePacket (/app/node_modules/pg-protocol/dist/parser.js:122:29)
    at Parser.parse (/app/node_modules/pg-protocol/dist/parser.js:35:38)
    at Socket.<anonymous> (/app/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:191:23) {
  length: 98,
  severity: 'ERROR',
  code: '42P07',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'heap.c',
  line: '1149',
  routine: 'heap_create_with_catalog'
}

If I leave the postgresql database empty, it creates the tables. At this point, it doesn't look like there is a migration for now as it seems NPM doesn't try to use the exiting tables if they exit, only create new ones.

I don't feel like recreating everything from scratch, so I guess I'll be waiting as I don't know enough about database migrations to be of much help.

@dasunsrule32 commented on GitHub (Dec 29, 2024): I tried a straight `pgloader` move and it failed. ``` pgloader -vvv sqlite://database.sqlite pgsql://npm:password@192.168.5.3/npm ``` Only failure is: ``` 2024-12-29T21:33:51.167006Z ERROR Database error 22008: date/time field value out of range: "1712521072575-01-01" CONTEXT: COPY migrations, line 1, column migration_time: "1712521072575-01-01" ``` Following errors in NPM: ``` [12/29/2024] [4:39:28 PM] [Global ] › ℹ info Using Postgres configuration [12/29/2024] [4:39:29 PM] [Migrate ] › ℹ info Current database version: none [12/29/2024] [4:39:29 PM] [Migrate ] › ℹ info [initial-schema] Migrating Up... migration file "20180618015850_initial.js" failed migration failed with error: create table "auth" ("id" serial primary key, "created_on" timestamptz not null, "modified_on" timestamptz not null, "user_id" integer not null, "type" varchar(30) not null, "secret" varchar(255) not null, "meta" json not null, "is_deleted" integer not null default '0') - relation "auth" already exists [12/29/2024] [4:39:29 PM] [Global ] › ✖ error create table "auth" ("id" serial primary key, "created_on" timestamptz not null, "modified_on" timestamptz not null, "user_id" integer not null, "type" varchar(30) not null, "secret" varchar(255) not null, "meta" json not null, "is_deleted" integer not null default '0') - relation "auth" already exists error: create table "auth" ("id" serial primary key, "created_on" timestamptz not null, "modified_on" timestamptz not null, "user_id" integer not null, "type" varchar(30) not null, "secret" varchar(255) not null, "meta" json not null, "is_deleted" integer not null default '0') - relation "auth" already exists at Parser.parseErrorMessage (/app/node_modules/pg-protocol/dist/parser.js:283:98) at Parser.handlePacket (/app/node_modules/pg-protocol/dist/parser.js:122:29) at Parser.parse (/app/node_modules/pg-protocol/dist/parser.js:35:38) at Socket.<anonymous> (/app/node_modules/pg-protocol/dist/index.js:11:42) at Socket.emit (node:events:518:28) at addChunk (node:internal/streams/readable:561:12) at readableAddChunkPushByteMode (node:internal/streams/readable:512:3) at Readable.push (node:internal/streams/readable:392:5) at TCP.onStreamRead (node:internal/stream_base_commons:191:23) { length: 98, severity: 'ERROR', code: '42P07', detail: undefined, hint: undefined, position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'heap.c', line: '1149', routine: 'heap_create_with_catalog' } ``` If I leave the `postgresql` database empty, it creates the tables. At this point, it doesn't look like there is a migration for now as it seems NPM doesn't try to use the exiting tables if they exit, only create new ones. I don't feel like recreating everything from scratch, so I guess I'll be waiting as I don't know enough about database migrations to be of much help.

kerem commented

2026-02-26 07:36:41 +03:00

Author

Owner

@dasunsrule32 commented on GitHub (Dec 30, 2024):

Ok, fiddled with it a bit more and got it working with pgloader. The only thing I had to do was re-create my admin user, but all other data is there and it's working correctly from what I can tell.

Configure NPM to use postgres then start it, let it create the tables. Stop NPM, then load the sqlite database into postgres.

Create a file called npm.load with the following content. Update the information needed. Download the database.sqlite from your server and run it in the same directory as the npm.load file.

Then run: pgloader -vvv npm.load

load database
     from sqlite://database.sqlite
     into postgresql://<user>:<password>@<your-pg-instance>:5432/<database>
     WITH quote identifiers, data only
;

You'll get similar output to below when completed:

2024-12-30T18:50:41.218028Z LOG report summary reset
             table name     errors       read   imported      bytes      total time       read      write
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
                  fetch          0          0          0                     0.000s    
        fetch meta data          0         17         17                     0.035s    
      Drop Foreign Keys          0          0          0                     0.000s    
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
           "migrations"          1         16          0                     0.087s     0.004s  
      "migrations_lock"          1          1          0                     0.087s     0.004s  
                 "auth"          1          1          0                     0.086s     0.001s  
                 "user"          1          2          0                     0.086s     0.001s  
      "user_permission"          1          2          0                     0.174s    
           "proxy_host"          0         42         42    10.5 kB          0.211s     0.002s     0.001s
     "redirection_host"          0          0          0                     0.161s     0.001s  
               "stream"          0          4          4     0.4 kB          0.273s    
          "certificate"          0          6          6     2.4 kB          0.283s     0.001s  
            "audit_log"          0        493        493   378.6 kB          0.454s     0.005s     0.019s
            "dead_host"          0          0          0                     0.169s    
          "access_list"          0          1          1     0.1 kB          0.290s     0.001s  
     "access_list_auth"          0          0          0                     0.290s    
              "setting"          1          1          0                     0.389s    
   "access_list_client"          0         25         25     1.7 kB          0.449s     0.001s     0.001s
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
COPY Threads Completion          0          4          4                     0.461s    
        Reset Sequences          0         14         14                     0.134s    
    Create Foreign Keys          0          0          0                     0.000s    
       Install Comments          0          0          0                     0.000s    
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
      Total import time          6        594        571   393.7 kB          0.595s

NPM Log output showing the postgres as the database server:

[12/30/2024] [1:58:21 PM] [Global   ] › ℹ  info      Using Postgres configuration
[12/30/2024] [1:58:22 PM] [Migrate  ] › ℹ  info      Current database version: 20211108145214

@dasunsrule32 commented on GitHub (Dec 30, 2024): Ok, fiddled with it a bit more and got it working with `pgloader`. The only thing I had to do was re-create my admin user, but all other data is there and it's working correctly from what I can tell. Configure NPM to use `postgres` then start it, let it create the tables. Stop NPM, then load the `sqlite` database into `postgres`. Create a file called `npm.load` with the following content. Update the information needed. Download the `database.sqlite` from your server and run it in the same directory as the `npm.load` file. Then run: `pgloader -vvv npm.load` ``` load database from sqlite://database.sqlite into postgresql://<user>:<password>@<your-pg-instance>:5432/<database> WITH quote identifiers, data only ; ``` You'll get similar output to below when completed: ``` 2024-12-30T18:50:41.218028Z LOG report summary reset table name errors read imported bytes total time read write ----------------------- --------- --------- --------- --------- -------------- --------- --------- fetch 0 0 0 0.000s fetch meta data 0 17 17 0.035s Drop Foreign Keys 0 0 0 0.000s ----------------------- --------- --------- --------- --------- -------------- --------- --------- "migrations" 1 16 0 0.087s 0.004s "migrations_lock" 1 1 0 0.087s 0.004s "auth" 1 1 0 0.086s 0.001s "user" 1 2 0 0.086s 0.001s "user_permission" 1 2 0 0.174s "proxy_host" 0 42 42 10.5 kB 0.211s 0.002s 0.001s "redirection_host" 0 0 0 0.161s 0.001s "stream" 0 4 4 0.4 kB 0.273s "certificate" 0 6 6 2.4 kB 0.283s 0.001s "audit_log" 0 493 493 378.6 kB 0.454s 0.005s 0.019s "dead_host" 0 0 0 0.169s "access_list" 0 1 1 0.1 kB 0.290s 0.001s "access_list_auth" 0 0 0 0.290s "setting" 1 1 0 0.389s "access_list_client" 0 25 25 1.7 kB 0.449s 0.001s 0.001s ----------------------- --------- --------- --------- --------- -------------- --------- --------- COPY Threads Completion 0 4 4 0.461s Reset Sequences 0 14 14 0.134s Create Foreign Keys 0 0 0 0.000s Install Comments 0 0 0 0.000s ----------------------- --------- --------- --------- --------- -------------- --------- --------- Total import time 6 594 571 393.7 kB 0.595s ``` NPM Log output showing the `postgres` as the database server: ``` [12/30/2024] [1:58:21 PM] [Global ] › ℹ info Using Postgres configuration [12/30/2024] [1:58:22 PM] [Migrate ] › ℹ info Current database version: 20211108145214 ```

kerem commented

2026-02-26 07:36:41 +03:00

Author

Owner

@dasunsrule32 commented on GitHub (Dec 30, 2024):

Just leaving it open for others to see and check out if there are any issues with this migration. I kept my sqlite db in case... Make sure to keep our sqlite database around in case...

@dasunsrule32 commented on GitHub (Dec 30, 2024): Just leaving it open for others to see and check out if there are any issues with this migration. I kept my sqlite db in case... Make sure to keep our sqlite database around in case...

kerem commented

2026-02-26 07:36:41 +03:00

Author

Owner

@reply2future commented on GitHub (Jan 6, 2025):

Thank you for your migration solution, it helps me a lot!

But I added a clear data steps to prevent input data error because of duplicate primary key, so I don't have to re-create the account or change the password of the default user.

TRUNCATE table setting;
TRUNCATE table "user";
TRUNCATE table auth;
TRUNCATE table user_permission;

And then I import the data by running pgloader -vvv npm.load. Everything works as before.

@reply2future commented on GitHub (Jan 6, 2025): Thank you for your migration solution, it helps me a lot! But I added a clear data steps to prevent input data error because of duplicate primary key, so I don't have to re-create the account or change the password of the default user. ```sql TRUNCATE table setting; TRUNCATE table "user"; TRUNCATE table auth; TRUNCATE table user_permission; ``` And then I import the data by running `pgloader -vvv npm.load`. Everything works as before.

kerem commented

2026-02-26 07:36:41 +03:00

Author

Owner

@dasunsrule32 commented on GitHub (Jan 9, 2025):

This is great! Thanks. :)

@dasunsrule32 commented on GitHub (Jan 9, 2025): This is great! Thanks. :)

kerem commented

2026-02-26 07:36:41 +03:00

Author

Owner

@github-actions[bot] commented on GitHub (Jul 25, 2025):

Issue is now considered stale. If you want to keep it open, please comment 👍

@github-actions[bot] commented on GitHub (Jul 25, 2025): Issue is now considered stale. If you want to keep it open, please comment :+1:

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

starred/nginx-proxy-manager-NginxProxyManager#2750

No description provided.

Rows
Columns