Transferring BTCPayServer data from one server to another

btcpay-migration.md

First, update your old BTCPayServer to the newest version. This way there aren't any database schema conflicts with the your server.

SSH to old server you are migrating from. Create a dump of the your database's schema and data

docker exec generated_postgres_1 pg_dumpall -c -U postgres > backup.sql

Try opening that backup.sql file in your text editor of choice. You will see it's just a series of SQL statements which:

• Sets some Postgres configuration
• Drops any databases, users and roles that existed previously
• Creates a btcpayservermainnet database and a postgres role
• Creates a bunch of tables
• Copies all the records in that you had on your old server's database

Open a new terminal on your dev machine (not one of the servers) and copy file from your old server to your new server using scp. This assumes you can ssh into each of your servers from your dev machine ...

scp -3 user@old-server-ip:path/to/backup.sql user@new-server-ip:where/to/copy/backup.sql

SSH to the new machine. Execute the backup.sql file in the Postgres docker container:

cat backup.sql | sudo docker exec -i generated_postgres_1 psql -Upostgres

This fails for me because the database has other connections it can't close ...

ERROR:  database "btcpayservermainnet" is being accessed by other users
DETAIL:  There is 1 other session using the database.
ERROR:  current user cannot be dropped
ERROR:  role "postgres" already exists
ALTER ROLE
ERROR:  database "btcpayservermainnet" already exists
REVOKE
GRANT
You are now connected to database "btcpayservermainnet" as user "postgres".
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
ERROR:  relation "AddressInvoices" already exists
ALTER TABLE
...

So I had to close all open connections manually. First found and closed the connection:

Open a shall prompt in the docker container as the postgres user $ sudo docker exec -it -u postgres generated_postgres_1 /bin/bash

Open up psql prompt

# psql

Find the open connection(s)

postgres=# select pid from pg_stat_activity where datname = 'btcpayservermainnet';
 pid 
-----
  28
(1 row)

Hit control-d to exit postgres to docker. Kill that process

# kill 28

Control-d to exit docker to VM. Try again:

$ cat backup.sql | sudo docker exec -i generated_postgres_1 psql -Upostgres

This should work. You might have to restart the new server just to be sure ...