Postgres

Usage

Most commands are found by \? e.g.

\dt - list tables
\l  - list databases
\c  - connect to db
\du - list users/roles

Since PostgreSQL 10, psql has \gx which is equal to mysql \G

select * from sometable \gx

ref:stackex

Basic User Access

E.g. you are testing a program w/db as a desktop user.

su - postgres
createuser --pwprompt user
(still as postgres.......)
createdb -O user mydb

In psql as postgres you must

ALTER USER user CREATEDB;

otherwise, the user account can't create a db (though he can delete). Finally, as the logged in user account you can:

psql -d postgres -c "DROP DATABASE   mydb;"
psql -d postgres -c "CREATE DATABASE mydb;"

If you were making a db program with user access, you would do something with roles (Grant (ALL,select,insert,etc) on db/tablename to username)

Bash create/delete

######## db_create.sh
psql -d postgres -c "CREATE DATABASE mydb;"
psql -d mydb -c " CREATE TABLE entry ( \
  id SERIAL PRIMARY KEY, \
  entryvalue TEXT \
);"


######## db_delete.sh
psql -d postgres -c "DROP DATABASE   mydb;"

I don't understand why sometimes you must specify -d postgres and sometimes you can specify the specific db. In practice, I suppose it isn't important.

Backup / Restore DB

backup

This will run the dump as the postgres user via su, and avoids some of the permissions errors that can creep up.

# cd /tmp
(just need a writeable directory, aka 777)
# su -c 'pg_dump mydb >> mydb_dump_2020.sql' postgres

assuming you have a postgres admin user, with all rights, this will backup the db. If you are remote, you might need something similar to (localhost/ip, ports, etc specified):

pg_dump -U username -h localhost databasename >> sqlfile.sql

ref: https://stackoverflow.com/questions/37984733/postgresql-database-export-to-sql-file

restore

Create the new db:

su - postgres
psql
create database bbbtest;
grant all privileges on database bbbtest to mypguser;
ALTER DATABASE bbbtest OWNER TO mypguser;

and postgres permissions are a disaster online for getting info on (unforunately, there is nothing like mysql's mysql -uroot -p -e "grant all on db.* to 'user'@localhost identified by 'pass';"). But what does work is permissions for specific tables need to be enabled for the user, so:

GRANT ALL ON sometableindb TO mypguser;
\quit
exit

Now, with that file

# su -c 'psql -d newdbname --set ON_ERROR_STOP=on -f ./mydb_dump_2020.sql' postgres

If you get errors, adjust data as needed or remove the ON_ERROR_STOP (some can be ignored, some can not - use discretion). At least in some cases, you can move data without worrying about postgres versions. I executed a pg_dump from 11.# and imported to 9.6 without issue. Devuan Beowulf to Ascii.

TIP: if you don't specify -d, then the data will get uploaded to one of the preexisting DB. Not good.

Another approach:

-- su - postgres
-- -- psql
-- -- -- create database bbbtest;
-- -- -- grant all privileges on database bbbtest to root;
-- -- -- \quit
-- -- exit
-- psql bbbtest < dbmake.sql
-- -- select * from A1;

Verify user permissions and upload was correct

su - mypguser
psql
select * from sometable;

See notes about table permissions above.

Create a user

• https://wiki.debian.org/PostgreSql

Note that if you have an existing db, you might want to add ownership to this user, or r/w permissions.

Reset User Password

This is never as easy as it should be with databases.

Edit pg_hba.conf to allow trust authorization temporarily
change the postgres method (last column) to trust
restart postgres
sudo su postgres
psql db_name
ALTER ROLE postgres WITH PASSWORD 'newpassword'; 
Edit pg_hba.conf again to restore the previous settings
restart postgres

Why can't this be a one-liner? Isn't automation the whole point of computers...?

Resources

• https://en.wikibooks.org/wiki/PostgreSQL
• https://wiki.debian.org/PostgreSql
• http://web.archive.org/web/20221129022928/https://blog.frankel.ch/poor-man-api/ - postgres, easy to deploy http api. Note that in golang, there are also quite easy ways to create an http server. Python as well, i'm sure.