Create new local server in pgadmin?

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.

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/

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

Usually a “connection refused” error indicates that the database server is either 1) not running, or 2) configured in such a way that it is not listening to the right port or IP address. Be sure to check ps -ef to see if Postgres is running, and also look at postgresql.conf to see if port and listen_addresses are set properly.

make sure the postgres service is running.
example in Linux : systemctl enable postgresql.service

Why? Attempting to connect to the server that is not running.

Action: Start your PostgreSQL server.

The path in the command below points to the data directory configured during the installation of PostgreSQL. Run this in Windows cmd.

pg_ctl -D "C:\user\PostgreSQL\data" start

>server started

After this go to pgAdmin and follow the initial steps as in question.

1. Create Server
2. Under General: Name: mytestServer
3. Under Connection: Hostname: localhost, Password: yourPassword, keep other settings as default
4. Hit save