Postgres commands

Table of Contents

Linode Installation and Getting started

Installing

sudo apt-get install postgresql postgresql-contrib

Install pgAdmin:

Official site

Start the server:

sudo systemctl start postgresql.service

sudo service postgresql start/stop/restart/status

Check the status:

sudo systemctl is-active postgresql

sudo pg_isready

Access the PostgreSQL database shell

By this command your are changing the user to “postgres”

By just using “psql” you are accessing database called “postgres” only

sudo su - postgres
psql

Open database

psql testdatabase

List all the users in database:

database=# \du

Creating new server and user

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

Creating new user interactively:

postgres=# createuser --interactive;

For checking the version postgres:

=# select version();

Error

psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: role “atulya” does not exist

Solution:

postgres=# create role atulya with createdb login password 'password1';

For error can’t connect:

$ sudo nano /etc/postgresql/14/main/pg_ident.conf
Map your user by adding this line

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
user1             <computer-username>               postgres

Then go ahead to the pg_hba.conf

$ sudo nano /etc/postgresql/14/main/pg_hba.conf
Add your postgre user, with method=peer, as shown below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer

Error about repository not found?

Stackoverflow Solution

run pgAdmin(In podman container):

Stackoverflow Solution

$ sudo mkdir /var/lib/pgadmin
$ sudo mkdir /var/log/pgadmin
$ sudo chown $USER /var/lib/pgadmin
$ sudo chown $USER /var/log/pgadmin

# Create virtual environment
$ python3 -m venv pgadmin4

$ source pgadmin4/bin/activate

# Install pgadmin4
(pgadmin4) $ pip install pgadmin4

# Start pgadmin4
(pgadmin4) $ pgadmin4

Error:

python3 -m venv pgadmin4 The virtual environment was not created successfully because ensurepip is not available. On Debian/Ubuntu systems, you need to install the python3-venv package using the following command.

apt install python3.10-venv

You may need to use sudo with that command. After installing the python3-venv package, recreate your virtual environment.

Solution:

sudo apt install python3.10-venv

Change the password:

sudo passwd postgres

Creation and deletion of database

createdb testdatabase;

drop testdatabase;

SQL Language

Sql tutorials

Running the complete database:

1996  toolbox enter database-atul-ka 
1997  source pgadmin4/bin/activate
1998  sudo pg_isready 
1999  pgadmin4
Tags :