Difference between revisions of "Postgres"

From Steak Wiki
Jump to navigationJump to search
(Created page with " ==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.")
 
 
(21 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===
 +
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:
 +
<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 8: 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

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