Difference between revisions of "Postgres"
| (15 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| + | ==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== | ||
| + | <pre> | ||
| + | ######## 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;" | ||
| + | </pre> | ||
| + | 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 / Restore DB== | ||
| Line 16: | Line 61: | ||
create database bbbtest; | create database bbbtest; | ||
grant all privileges on database bbbtest to mypguser; | 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 | \quit | ||
exit | exit | ||
| Line 37: | Line 85: | ||
-- -- select * from A1; | -- -- select * from A1; | ||
</pre> | </pre> | ||
| + | |||
| + | ====Verify user permissions and upload was correct==== | ||
| + | <pre> | ||
| + | su - mypguser | ||
| + | psql | ||
| + | select * from sometable; | ||
| + | </pre> | ||
| + | See notes about table permissions above. | ||
==Create a user== | ==Create a user== | ||
| Line 43: | Line 99: | ||
Note that if you have an existing db, you might want to add ownership to this user, or r/w | Note that if you have an existing db, you might want to add ownership to this user, or r/w | ||
permissions. | 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. | ||
Latest revision as of 06:09, 18 December 2023
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
- 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.
