Difference between revisions of "Postgres"

From Steak Wiki
Jump to navigationJump to search
 
(19 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==
 +
===backup===
 
This will run the dump as the postgres user via su, and avoids some of the permissions errors that can creep up.
 
This will run the dump as the postgres user via su, and avoids some of the permissions errors that can creep up.
 
  # cd /tmp
 
  # cd /tmp
Line 9: Line 55:
 
  pg_dump -U username -h localhost databasename >> sqlfile.sql
 
  pg_dump -U username -h localhost databasename >> sqlfile.sql
 
ref: https://stackoverflow.com/questions/37984733/postgresql-database-export-to-sql-file
 
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:
 +
<pre>
 +
-- su - postgres
 +
-- -- psql
 +
-- -- -- create database bbbtest;
 +
-- -- -- grant all privileges on database bbbtest to root;
 +
-- -- -- \quit
 +
-- -- exit
 +
-- psql bbbtest < dbmake.sql
 +
-- -- select * from A1;
 +
</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 15: 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