Setting up your local install

Connecting to postgres

You should have

• a running postgresql 17 or 16 install on your machine
• and/or pgAdmin installed

Server vs databases

You connect to a postgres server

On that server you may have zero, one or multiple databases.

When you launch postgres on your local, you are actually launching the postgres server.

You connect and query the database on the command line using psql or in pgAdmin.

Users

The postgres user is the super user.

It can do anything on the server, create and manage databases, users, drop everything etc … handle with caution.

Installing postgreSQL also creates a user with your machine user name : alexis in my case

The username (alexis) user is not as powerful as the postgres user

Connecting to the server

You can connect in the terminal or with pgAdmin

In the terminal you use psql to connect

psql takes the following parameters

• -U username
• -h host (IP address or local host)
• -p port
• -d database_name

PostgreSQL port is by default 5432 and does not have to be specified each time.

Examples

So to connect to a local instance of a postgres server with user bob and database database_name

psql -U bob  -d database_name

To connect to a remote server IP: 123.456.789.246, same user and database

psql -U bob -h 123.456.789.246 -d database_name

How to check your connection

to see your current connection run

psql -c "\conninfo"

if it complains, add postgres as the database name

psql -c "\conninfo" -d postgres

This should return:

You are connected to database “postgres” as user “alexis” via socket in “/tmp” at port “5432”.

how to connect to the server in pgAdmin

1. Open pgAdmin
2. Right-click on “Servers” in the left panel and choose “Create” ```bash “Server…”
3. In the “Create - Server” dialog:

General tab:

Name: Give it a name like “Local PostgreSQL”

Connection tab:

• Host name/address: localhost
• Port: 5432 (default PostgreSQL port)
• Maintenance database: postgres
• Username: [your_username]
• Password: Leave blank if you haven’t set one

to connect to a remote server, just change the connection tab parameters (host, user, password)

On windows

• Install postgres and follow the instructions
• click on the psql.exe file

• it will open a terminal window and prompt you with hostname

  • if you’re connecting on local just press enter
  • if you’re connecting to remote add the IP address

Same with the other parameters

server configuration

There are 2 configuration files for a postgres server

• postgresql.conf : manages how the server operates
• pg_hba.conf : manages who can connect and how they authenticate

find the location of the pg_hba.conf file with show hba_file;

and the same for the postgresql.conf file with show config_file;

postgresql.conf

General server configuration

This file controls most of the global settings for the PostgreSQL server. It includes:

• Resource allocation (memory, CPU)
• Default storage locations
• Replication settings
• Client connection defaults
• Query planner settings
• Logging and statistics
• Autovacuum settings
• Client/server communication parameters
• Locale and formatting
• Error handling

Key points:

• Affects the overall behavior and performance of the PostgreSQL server
• Changes typically require a server restart to take effect
• Located in the data directory

Example settings:

• max_connections = 100
• shared_buffers = 128MB
• log_destination = 'stderr'

pg_hba.conf

Role: Client authentication control

This file controls how clients are allowed to connect to the server. “HBA” stands for “host-based authentication”. It specifies:

• Which hosts can connect
• Which database they can connect to
• Which PostgreSQL user names they can use
• How clients are authenticated (password, ident, trust, etc.)

Key points:

• Controls access at a very granular level
• Changes can typically be loaded with a simple reload, not requiring a full restart
• Critical for security management
• Also located in the data directory

Example entries:

# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
host all all 127.0.0.1/32 md5
host production app_user 192.168.1.0/24 scram-sha-256

.psqlrc

.psqlrc is a configuration file for the psql command-line interface in PostgreSQL.

It allows you to customize your psql environment and set default behaviors.

• Usually located in your home directory: ~/.psqlrc on Unix-like systems
• On Windows: %APPDATA%\postgresql\psqlrc.conf
• Customizes the psql environment
• Sets default options and behaviors
• Runs commands automatically when psql starts

Example

always timing the queries

• pager mode
• expanded mode

1. Set default pager: \pset pager always
2. Set line style: \pset linestyle unicode
3. Set timing on: \timing
4. Set expanded auto mode: \x auto
5. Custom prompt: \set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%#
6. History settings: \set HISTSIZE 2000
7. History settings: \set HISTCONTROL ignoredups
8. Enable verbose error reports: \set VERBOSITY verbose