Category: Client

PGAdmin GUI Server Group Connections List Repository

A client opens PGAdmin GUI with the following messages:

{"success":0,"errormsg":"(sqlite3.OperationalError) no such table: user\n[SQL: SELECT anon_1.user_id AS anon_1_user_id, anon_1.user_email AS anon_1_user_email, anon_1.user_username AS anon_1_user_username, anon_1.user_password AS anon_1_user_password, anon_1.user_active AS anon_1_user_active, anon_1.user_confirmed_at AS anon_1_user_confirmed_at, anon_1.user_masterpass_check AS anon_1_user_masterpass_check, anon_1.user_auth_source AS anon_1_user_auth_source, role_1.id AS role_1_id, role_1.name AS role_1_name, role_1.description AS role_1_description \nFROM (SELECT user.id AS user_id, user.email AS user_email, user.username AS user_username, user.password AS user_password, user.active AS user_active, user.confirmed_at AS user_confirmed_at, user.masterpass_check AS user_masterpass_check, user.auth_source AS user_auth_source \nFROM user \nWHERE user.id = ?\n LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN (roles_users AS roles_users_1 JOIN role AS role_1 ON role_1.id = roles_users_1.role_id) ON anon_1.user_id = roles_users_1.user_id]\n[parameters: ('1', 1, 0)]\n(Background on this error at: http://sqlalche.me/e/13/e3q8)","info":"","result":null,"data":null}

This message means the repository of PostgreSQL database connections details including credentials of Server Group is unavailable for PGAdmin.

The repository of PostgreSQL database connections details including credentials of Server Group is stored in SQLLite 3 format under “%UserProfile%\AppData\Roaming\pgAdmin\pgadmin4.db”.

It is strongly suggested to backup this file frequently if you have a long list of Server Group connections.

Advertisement

How to Configure SQL Developer to Connect to PostgreSQL

A client likes to use Oracle SQL Developer to connect to PostgreSQL databases. During the configuring, some errors occurred. This post shows how to resolve the configuration issues from Oracle SQL Developer and third party libraries .

The typical issue is you will get the following errors when connecting to PostgreSQL database by using non ‘postgres’ users.

Failure -FATAL: database "user1" does not exist

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

So it is not correct that we have to use ‘postgres’ username which is the same name as database name to connect to PostgreSQL databases. Instead, any user can be used to connect to a PostgreSQL database.

WARNING: Console code page (850) differs from Windows code page (1252)

Set the code page by entering “C:> chcp 1252”.

When trying to connect to my first PostgreSQL database, got the following warning :

C:>psql -h 192.168.78.51 -U postgres -d postgres
Password for user postgres:
psql (9.6.3)
WARNING: Console code page (850) differs from Windows code page (1252)
 8-bit characters might not work correctly. See psql reference
 page "Notes for Windows users" for details.
Type "help" for help.

Below are extracted from psql reference page “Notice for Windows users”.

Notes for Windows Users

psql is built as a "console application". Since the Windows console windows use a different encoding than the rest of the system, you must take special care when using 8-bit characters within psql. If psql detects a problematic console code page, it will warn you at startup. To change the console code page, two things are necessary:

Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code page that is appropriate for German; replace it with your value.) If you are using Cygwin, you can put this command in /etc/profile.
Set the console font to Lucida Console, because the raster font does not work with the ANSI code page.

Set the code page to 1252 , and connect to server successfully.

C:>chcp 1252
Active code page: 1252

C:>psql -h 192.168.78.51 -U postgres -d postgres
Password for user postgres:
psql (9.6.3)
Type "help" for help.

postgres=#

psql: could not connect to server: Connection refused

To avoid “Connection refused”, Configuration is required for client to access PostgreSQL database server.

When trying to connect to a new PostgreSQL database, the following error occurred:

C:\Program Files (x86)\pgAdmin 4\v1\runtime>psql -h 192.168.78.51 -d postgres
psql: could not connect to server: Connection refused (0x0000274D/10061)
 Is the server running on host "192.168.78.51" and accepting
 TCP/IP connections on port 5432?
Check postgresql server is up and running 
$ systemctl status postgresql-9.6

● postgresql-9.6.service - PostgreSQL 9.6 database server
 Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; enabled; vendor preset: disabled)
 Active: active (running) since Sun 2017-07-16 18:08:47 AEST; 15min ago
 Process: 1238 ExecStartPre=/usr/pgsql-9.6/bin/postgresql96-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 1243 (postmaster)
 CGroup: /system.slice/postgresql-9.6.service
 ├─1243 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
 ├─1246 postgres: logger process
 ├─1248 postgres: checkpointer process
 ├─1249 postgres: writer process
 ├─1250 postgres: wal writer process
 ├─1251 postgres: autovacuum launcher process
 ├─1252 postgres: stats collector process
 └─1822 postgres: postgres postgres 192.168.78.1(56572) idle
From client telnet to server
C:\Users\postgres>telnet 192.168.78.51 5432
Connecting To 192.168.78.51...Could not open connection to the host, on port 5432: Connect failed
From server check listener port 5432 not opened on IP 192.168.78.1
$ netstat -ntl|grep 5432
tcp  0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
tcp6 0 0       ::1:5432 :::*      LISTEN
Allow remote IP address to access PostgreSQL
$ su - postgres
$ vi /var/lib/pgsql/9.6/data/pg_hba.conf

...
..
.

# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 192.168.78.51/24 md5
...
..
.
Edit /var/lib/pgsql/9.6/data/postgresql.conf

# – Connection Settings –

...
..
.
listen_addresses = '192.168.78.51,localhost' # what IP address(es) to listen on;
 # comma-separated list of addresses;
 # defaults to 'localhost'; use '*' for all
 # (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
...
..
.
Stop and Start PostgreSQL server
$systemctl status postgresql-9.6

$systemctl stop postgresql-9.6

$systemctl start postgresql-9.6
Connect to PostgreSQL database successfully
C:\Program Files (x86)\pgAdmin 4\v1\runtime>psql -h 192.168.78.51 -d postgres -U postgres
Password for user postgres:
psql (9.6.3)
WARNING: Console code page (850) differs from Windows code page (1252)
 8-bit characters might not work correctly. See psql reference
 page "Notes for Windows users" for details.
Type "help" for help.

postgres=# \l
 List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
(3 rows)