How to install PostgreSQL on Debian 12

[myVesta]

Install:

Code: Select all

apt update
apt -y install postgresql postgresql-contrib
apt -y install php8.2-pgsql

Create testuser and testdb with testpass:

Code: Select all

cd /
sudo -u postgres psql <<'EOF'
CREATE USER testuser WITH PASSWORD 'testpass';
CREATE DATABASE testdb OWNER testuser;
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
EOF

Test connection to testdb:

Code: Select all

psql -U testuser -d testdb -h localhost

(you will be prompted for a password)



Access database without password:

Code: Select all

sudo -u postgres psql -d testdb

Create a user:

Code: Select all

sudo -u postgres psql -c "CREATE USER testuser WITH PASSWORD 'testpass';"

Create a database:

Code: Select all

sudo -u postgres createdb testdb

Create a database that has an owner:

Code: Select all

sudo -u postgres createdb testdb -O testuser

Grant access:

Code: Select all

sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;"

Dump a database:

Code: Select all

sudo -u postgres pg_dump testdb > testdb.sql

Restore a database:

Code: Select all

sudo -u postgres psql -d testdb < testdb.sql

Drop a user:

Code: Select all

sudo -u postgres psql -c "DROP OWNED BY testuser;"
sudo -u postgres psql -c "REASSIGN OWNED BY testuser TO postgres;"
sudo -u postgres psql -c "DROP USER testuser;"

Drop a database:

Code: Select all

sudo -u postgres psql -c "DROP DATABASE testdb;"

List all databases:

Code: Select all

sudo -u postgres psql -l