PostgreSQL Commands & Performance Optimization

2024-03-29 · PostgreSQL & DB · By Zeeshan Ahmad

To check if PostgreSQL is installed on your system, follow these steps based on your operating system:

On Windows

#1. Check via Command Prompt (cmd) or PowerShell

Open Command Prompt (Win + R, type cmd, and press Enter) or PowerShell.
Run:

psql --version

If PostgreSQL is installed, you will see the version, e.g.:

psql (PostgreSQL) 15.2

If not installed, you will see an error like:

'psql' is not recognized as an internal or external command...

#2. Check via Services

Press Win + R, type services.msc, and press Enter.
Look for PostgreSQL service (e.g., postgresql-x64-15).
If found and running, PostgreSQL is installed.

#3. Check in Installed Programs

Open Control Panel → Programs and Features.
Look for PostgreSQL in the installed programs list.

On Linux (Ubuntu, Debian)

#1. Check via Terminal

Open a terminal (Ctrl + Alt + T) and run:

psql --version

If installed, it will return the PostgreSQL version.

#2. Check if the Service is Running

            
bash

sudo systemctl status postgresql

If installed and running, you will see output like:

Active: active (running) since ...

#3. Check Package Installation

dpkg -l | grep postgresql

If installed, it will list PostgreSQL packages.

#4. Check with apt (Debian/Ubuntu)

apt list --installed | grep postgresql

On macOS

#1. Check via Terminal

Run:

psql --version

If installed, you will see the PostgreSQL version.

#2. Check via Homebrew

If you installed PostgreSQL using Homebrew, run:

            
bash

brew list | grep postgresql

If installed, it will show postgresql in the list.

#3. Check if PostgreSQL is Running

            
bash

brew services list | grep postgresql

Alternative Method (Any OS)

If psql is not recognized, PostgreSQL might not be in your PATH. Try:

locate psql

find / -name psql 2>/dev/null

This will search for psql on your system.

Let me know if you need help installing PostgreSQL! 🚀

To connect to PostgreSQL on macOS, follow these steps:

#1. Check if PostgreSQL is Installed

Run:

psql --version

If you see a version like psql (PostgreSQL) 15.2, PostgreSQL is installed.

If not, install it using Homebrew:

            
bash

brew install postgresql

Then, start the PostgreSQL service:

            
bash

brew services start postgresql

#2. Start PostgreSQL Service

Ensure the PostgreSQL server is running:

            
bash

brew services start postgresql

pg_ctl -D /usr/local/var/postgres start

To check if PostgreSQL is running:

pg_isready

If it responds with accepting connections, PostgreSQL is running.

#3. Connect to PostgreSQL

A. Connect as the Default PostgreSQL User

By default, PostgreSQL creates a postgres user. Connect to it:

psql -U postgres

If it asks for a password and you haven’t set one, you may need to set it first.

To set a password:

psql -U postgres

Then, inside psql:

ALTER USER postgres PASSWORD 'yourpassword';

B. Connect to a Specific Database

If you already have a database (e.g., mydatabase), connect to it:

psql -U postgres -d mydatabase

C. Connect Using a Specific Host

If your database is running on a different host (default is localhost):

psql -U postgres -h 127.0.0.1 -d mydatabase

#4. Create a New Database (Optional)

If you need a new database:

createdb mydatabase -U postgres

Then connect to it:

psql -U postgres -d mydatabase

#5. Exit PostgreSQL

To exit psql, type:

#6. Troubleshooting

If you get psql: command not found, add PostgreSQL to your PATH:

            
bash

export PATH="/usr/local/opt/postgresql/bin:$PATH"

Add this to your ~/.zshrc or ~/.bash_profile to make it permanent.

If PostgreSQL is not running:

            
bash

brew services restart postgresql

Let me know if you need help! 🚀

The error message:

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "postgres" does not exist

indicates that PostgreSQL is running, but the default “postgres” role/user does not exist.

Fix This Issue on PostgreSQL 16 (macOS)

#1. Check if PostgreSQL is Running

Run:

            
bash

brew services list | grep postgresql

pg_ctl -D /opt/homebrew/var/postgresql@16 status

If it’s not running, start it with:

            
bash

brew services start postgresql@16

#2. Check Existing Roles

Run:

psql -U $(whoami) -d postgres

If successful, list all users:

\du

You might see an output like:

Role name | Attributes | Member of -----------+------------+---------- myuser | Superuser | {}

If postgres is missing, create it in the next step.

3. Create the “postgres” Role

Since PostgreSQL 16 does not automatically create the postgres role, manually add it.

First, log in as your macOS user (default owner of the PostgreSQL installation):

psql -U $(whoami) -d postgres

Then, create the postgres role:

CREATE ROLE postgres WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD 'yourpassword';

Verify:

\du

Exit:

#4. Try Connecting Again

psql -U postgres -d postgres

Alternative: Use Your macOS Username

Since PostgreSQL 16 uses your macOS username instead of postgres, try:

psql -U $(whoami) -d postgres

If this works, you can continue using this user instead of postgres.

#5. Set Your User as Default

If psql -U $(whoami) -d postgres works but psql -U postgres does not, you can set your user as default:

createuser -s $(whoami)

Now, just running psql should work.

#6. Restart PostgreSQL

If the above changes do not take effect, restart the PostgreSQL service:

            
bash

brew services restart postgresql@16

pg_ctl -D /opt/homebrew/var/postgresql@16 restart

Now, retry:

psql -U postgres

Let me know if you still face issues! 🚀

To create a PostgreSQL database on macOS (PostgreSQL 16), follow these steps:

#1. Ensure PostgreSQL is Running

Run:

            
bash

brew services start postgresql@16

or check its status:

            
bash

brew services list | grep postgresql

#2. Connect to PostgreSQL

Try connecting using your macOS username (since postgres might not exist):

psql -U $(whoami) -d postgres

or if you have a postgres user:

psql -U postgres -d postgres

#3. Create a Database

Inside psql, run:

CREATE DATABASE mydatabase;

Or from the terminal:

createdb -U $(whoami) mydatabase

If using postgres user:

createdb -U postgres mydatabase

#4. Verify the Database

List all databases:

#5. Connect to the New Database

psql -U $(whoami) -d mydatabase

psql -U postgres -d mydatabase

#6. Exit PostgreSQL

Troubleshooting

If you get:

createdb: error: database creation failed: ERROR: permission denied to create database

Fix it by granting permission:

psql -U $(whoami) -d postgres ALTER ROLE $(whoami) CREATEDB; \q

Now, try creating the database again.

Your database is now ready! 🎉 Let me know if you need more help. 🚀

To import data into PostgreSQL on macOS (PostgreSQL 16), follow these steps:

#1. Ensure PostgreSQL is Running

Check status:

            
bash

brew services list | grep postgresql

Start if necessary:

            
bash

brew services start postgresql@16

#2. Connect to PostgreSQL

Open psql and connect to your database:

psql -U $(whoami) -d mydatabase

psql -U postgres -d mydatabase

#3. Import Data

A. Import a CSV File

#1. Ensure the table exists:

CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100), age INT, email VARCHAR(255) );

2. Use COPY command (faster, but requires superuser):

COPY users(name, age, email) FROM '/Users/yourusername/Desktop/users.csv' DELIMITER ',' CSV HEADER;

🔹 Replace /Users/yourusername/Desktop/users.csv with your actual file path.

3. If COPY fails due to permission issues, try:

psql -U $(whoami) -d mydatabase -c "\copy users(name, age, email) FROM '/Users/yourusername/Desktop/users.csv' DELIMITER ',' CSV HEADER;"

B. Import a SQL Dump File

If you have a PostgreSQL .sql dump file:

psql -U $(whoami) -d mydatabase -f /Users/yourusername/Desktop/data.sql

#4. Verify Data Import

Check imported data:

SELECT FROM users LIMIT 10;

#5. Troubleshooting

Error: Permission Denied for COPY
Run:

bash

chmod 644 /Users/yourusername/Desktop/users.csv

Or move the file to /tmp/ and retry:

COPY users(name, age, email) FROM '/tmp/users.csv' DELIMITER ',' CSV HEADER;

Error: Relation “users” Does Not Exist
Ensure the table is created before importing.

Your data should now be imported successfully! 🚀 Let me know if you need help.

To import data into PostgreSQL using pg_restore on macOS (PostgreSQL 16), follow these steps:

#1. Ensure PostgreSQL is Running

Check status:

bash

brew services list | grep postgresql

Start if necessary:

bash

brew services start postgresql@16

#2. Locate Your Backup File

Ensure you have a .dump or .backup file, e.g., backup.dump. Move it to a known directory, e.g.:

mv ~/Downloads/backup.dump ~/Desktop/

#3. Create a New Database

Before restoring, create a database:

createdb -U $(whoami) mydatabase

or

createdb -U postgres mydatabase

#4. Restore Data Using pg_restore

Run:

pg_restore -U $(whoami) -d mydatabase --verbose ~/Desktop/backup.dump

or using the postgres user:

pg_restore -U postgres -d mydatabase --verbose ~/Desktop/backup.dump

#5. Alternative: If Database Already Exists

If mydatabase already exists and contains data, use:

pg_restore -U $(whoami) -d mydatabase --clean --if-exists --verbose ~/Desktop/backup.dump

This:
Drops objects if they exist (--clean --if-exists).
Restores the database.

#6. Verify Import

Connect to the database:

psql -U $(whoami) -d mydatabase

Check tables:

\dt

Preview data:

SELECT FROM your_table LIMIT 10;

#7. Troubleshooting

Error: “pg_restore: error: could not connect to database”

Ensure PostgreSQL is running:

            
bash

brew services start postgresql@16

Error: “pg_restore: [archiver] input file does not appear to be a valid archive”

Check if the file is a valid PostgreSQL dump:

file ~/Desktop/backup.dump

If not, it may be an SQL file. Restore using:

psql -U $(whoami) -d mydatabase -f ~/Desktop/backup.dump

Your database should now be restored! 🎉 Let me know if you need more help. 🚀