Postgres

From Steak Wiki
Jump to navigationJump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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

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

Retrieved from "http://steakwiki.com/index.php?title=Postgres&oldid=1207"