[QUESTION] Postgres migration

[dasunsrule32]

Hello, I see that Postgress support was added with #4254. I am wondering if there is a migration path from the default sqlite to postgres? Thanks. :)

[dasunsrule32]

I tried a straight pgloader move and it failed.

pgloader -vvv sqlite://database.sqlite pgsql://npm:[email protected]/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]

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]

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...

[reply2future]

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.

[dasunsrule32]

This is great! Thanks. :)