Create new local server in pgadmin?

Question

I have PostgreSQL 11 and PGadmin 4 installed on windows. Currently I'm connected to a AWS server which hosts all of my data.

I want to create a local server (localhost) as a testing environment where I can experiment. I can't seem to do it though, and the other similar questions on stack don't help. Here's what my process is:

1. in pgAdmin, right click 'Servers' and go Create>Server

2. On the 'Create - Server' pop up box, i type in Name: Localserver. For 'connection' I type localhost. Port I leave as default '5432', db: postgres, username: postgres password: empty

3. click save.

however, I get an error:

Unable to connect to server:

could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432?

could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

What should I do? I am the admin if that makes a difference.

Answer 1

As a new Postgres user, I did not understand how to make use of Postgres on Ubuntu. So I'm just going to chime in and help out other newbies who perhaps cannot figure out how to work with Postgres on Linux. If you're using Windows, steps should be similar.

Before you get to using PgAdmin, a bit of configuration is required. Most will happen in your terminal at first.

Open a terminal using Ctrl + Alt + T if you're on a PC. Or just pres ALT + F1 and begin typing Terminal.

Let's start with the basics first and make sure you have proper installation.

1. Installing Postgres Latest

1.1 update the system software packages

sudo apt update 

1.2 install latest version of PostgreSQL from default Ubuntu repositories

sudo apt install postgresql 

the installer will create a new PostgreSQL collection of databases that will be managed by a single server instance

Default data directory : /var/lib/postgresql/your-version/main

Configurations files : /etc/postgresql/your-version/main

2. Checking if Postgres Service is Installed

2.1 Check if Postgres is Active

sudo systemctl is-active postgresql 

You should see : active

2.2 Check if Postgres is enabled

sudo systemctl is-enabled postgresql 

You should see : enabled

2.3 Check Postgres Service status

sudo systemctl status postgresql 

You should see : active (exited) marked in green

2.4 Check if Postgres is ready to accept connections

sudo pg_isready 

You should see : /var/run/postgresql:5432 - accepting connections

3. Configuring Postgres Authentication

3.1 Opening the pg_hba.conf as SUPERUSER

sudo code --user-data-dir=~/root /etc/postgresql/13/main/pg_hba.conf 

I'm using visual studio code so for me code is vsc codename. If you're using vim or sublime just replace code with your text editor name.

3.2 Configuring pg_hba.conf

Notes: you shouldn't need to change anything here, just make sure your configuration files matches the following lines :

host    all             all             127.0.0.1/32            md5 # IPv6 local connections: host    all             all             ::1/128                 md5 

Hit save and close.

3.3 Restart Postgres Service

sudo systemctl restart postgresql 

4. Create NEW Server

For me, this is where all my confusion was. Before you use PgAdmin, you need to create a server in your terminal, then you can connect and manager it with PgAdmin just like you would with PhpMyAdmin. It's actually easier.

4.1 Access the PostgreSQL database shell

sudo su - postgres psql 

You will then see this : postgres=#

4.2 Creating new server and user

postgres=# create user bob with superuser password 'admin'; 

That's how you create new user and server in Postgres. Let's move on to PgAdmin.

5. Installing pgAdmin4

5.1 Add public key for the repository

curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add 

Notes : if you don't have curl your Ubuntu will give you the command to install it

5.2 create the repository configuration file

sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update' 

5.3 install pgAdmin4

sudo apt update sudo apt install pgadmin4 

5.4 run the web setup script installed with the pgadmin4 binary package

sudo /usr/pgadmin4/bin/setup-web.sh 

It will asks you for your email address and password. This email and password is required to login to the PgAdmin4 web interface

6. Accessing pgAdmin4 Web Interface

6.1 Open your favorite browser

type in the address of the PgAdmin web interface. It usually looks like this

http://127.0.0.1/pgadmin4

Note : After installation the web setup script will tell you exactly where to access the web interface.

When you see the login screen, enter the email and password you've chosen during the web setup script.

6.2 Adding New Server Connection

6.2.1 Click on Add New Server

6.2.2 Under General tab enter a name for your server. ANY name you would like, it doesn't matter. You could enter PgServer1 or whatever. Don't change anything else.

6.2.3 Switch to Connection Tab

Hostname/ Address : localhost Port : 5432 Maintenance database : postgres (always) Username :  **bob** (the username youve chosen at 4.2) Password : admin (or any password you chose at 4.2) 

Hit Save.

Voila! You should be connected successfully. If not, just open terminal and create a new user like we did at 4.2

Notes : to create databases is very easy just right click on your servername

create > databases 

Useful Resources & Tutorials

PostgreSQL Fundamentals : Queries and the likes

PostgreSQL & JSON : useful for dealing with JS apps

PostgreSQL & Nodejs : Create realtime apps with nodejs and socket.io

More PostgreSQL Nodejs

• https://github.com/supabase/realtime
• https://whatsyourssn.com/posts/real-time-app-socket-postgresql/

Answer 2

This is probably one of two things.

1. You have set up a Postgres server, but have not adjusted listen_addresses in your postgresql.conf file, or

2. You have not set up a Postgres server on your local machine. ("Create Server" is a bit misleading, it should probably be "Create Server Connection".)