How can I import my server list from pgAdmin 3 to pgAdmin 4?

Question

I wonder whether there is any way to import my server list from pgAdmin 3 to pgAdmin 4 (so that I don't have to create servers one by one again).

Answer 1

For Windows in C:\Users\%user%\AppData\Roaming\pgAdmin you can find pgadmin4.db which is where pgAdmin4 saves its configuration. It is SQLite format 3 and with SQLite browser you can open it and insert servers into server table and then save and they are added to pgAdmin4. Note that passwords are saved encrypted so leave NULL to input them later. Now you could make sql script that reads registry export from pgAdmin III and inserts into table for fully automated process.

Answer 2

Under linux (Ubuntu 16.04, in my case, but it must be there on other systems, too), you'll find a .pgadmin3 file in your home directory. In this config file there are entries like

[Servers/1]
Server=dezso.com
HostAddr=
Description=random
Service=
ServiceID=
DiscoveryID=
Port=5432
StorePwd=false
Rolename=
Restore=true
Database=postgres
Username=dezso
...

The 'Servers' part above refers to a server group, that is important to notice.

With some diligence, you can extract the necessary data from this in a suitable format (it can be even a bunch of INSERT statements, see below).

Under your home directory you will find a pgadmin4.db file (the exact location may depend on how you installed pgAdmin4). This is a SQLIte 3 database (as also described in the other answer), and there is a server table in it:

sqlite> .schema server
CREATE TABLE server (
        id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        servergroup_id INTEGER NOT NULL,
        name VARCHAR(128) NOT NULL,
        host VARCHAR(128) NOT NULL,
        port INTEGER NOT NULL CHECK (port >= 1024 AND port <= 65535),
        maintenance_db VARCHAR(64) NOT NULL,
        username VARCHAR(64) NOT NULL,
        ssl_mode VARCHAR(16) NOT NULL CHECK (
            ssl_mode IN (
                'allow', 'prefer', 'require', 'disable', 'verify-ca', 'verify-full'
                )),
        comment VARCHAR(1024), password TEXT(64), role text(64), discovery_id TEXT, hostaddr TEXT(1024), db_res TEXT, passfile TEXT, sslcert TEXT, sslkey TEXT, sslrootcert TEXT, sslcrl TEXT, sslcompression INTEGER default 0, bgcolor TEXT(10), fgcolor TEXT(10),
        PRIMARY KEY (id),
        FOREIGN KEY(user_id) REFERENCES user (id),
        FOREIGN KEY(servergroup_id) REFERENCES servergroup (id)
    );

You have to create the server group(s) first, that is better done from the UI, as it will tell you the user_id that you need for creating a server using INSERT INTO server ....