kamil.orchia.pl

Zalogowanie się do PostgreSQL za pomocą psql:

Logujemy się na root’a:

su - postgres -c 'psql template1'
Password:
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=#

Stworzenie użytkownika na bazie danych:

template1=# CREATE USER uzytkownik WITH ENCRYPTED PASSWORD 'nasz_haslo';
CREATE ROLE
template1=#

Zmiana hasła dla użytkownika:

template1=# ALTER USER uzytkownik WITH ENCRYPTED PASSWORD 'nowe_pass';
ALTER ROLE
template1=#

Usunięcie użytkownika z bazy danych:

template1=# DROP USER uzytkownik;
DROP ROLE
template1=#

Stworzenie instancji na bazie danych:

template1=# CREATE DATABASE nowa_baza;
CREATE DATABASE
template1=#

Stworzenie instancji na bazie danych i powiązanie jej z użytkownikiem:

template1=# CREATE DATABASE nowa_baza WITH OWNER = uzytkownik;
CREATE DATABASE
template1=#

Stworzenie bazy danych z kodowaniem UTF8 i powiązanie jej z użytkownikiem:

template1=# CREATE DATABASE nowa_baza WITH OWNER = uzytkownik ENCODING = 'UNICODE';
CREATE DATABASE
template1=#

Usunięcie instancji na bazie danych:

template1=# DROP DATABASE nowa_baza;
DROP DATABASE
template1=#

Nadanie praw tylko do odczytu:

dbname=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_ro;
GRANT
dbname=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO user_ro;
GRANT

Wyświetlenie tabel w bazie danych:

dbname=> \dt
                 List of relations
 Schema |           Name           | Type  | Owner
--------+--------------------------+-------+-------
 public | cmm_application          | table | user
 public | cmm_dictionary           | table | user
 public | cmm_principal            | table | user
 public | cmm_register             | table | user
...

Wyświetlenie listy użytkowników:

template1=# SELECT * FROM pg_user;
  usename   | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil |                              useconfig
------------+----------+-------------+----------+-----------+----------+----------+---------------------------------------------------------------------
 postgres   |       10 | t           | t        | t         | ******** |          |
 phpbb      |    16384 | f           | f        | f         | ******** |          |
 mediawiki  |    24576 | f           | f        | f         | ******** |          | {TimeZone=GMT,"DateStyle=ISO, YMD","search_path=mediawiki, public"}
 uzytkownik |    25166 | f           | f        | f         | ******** |          |
(4 rows)

template1=#

Wyświetlenie listy instancji bazodanowych:

template1=# SELECT * FROM pg_database;
  datname  | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig |               datacl        
-----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+-------------------------------------
 template1 |     10 |        6 | t             | t            |           -1 |         11510 |          380 |          1663 |           | 
{=c/postgres,postgres=CTc/postgres}
 template0 |     10 |        6 | t             | f            |           -1 |         11510 |          380 |          1663 |           | 
{=c/postgres,postgres=CTc/postgres}
 postgres  |     10 |        6 | f             | t            |           -1 |         11510 |          380 |          1663 |           |
 phpbb     |  16384 |        6 | f             | t            |           -1 |         11510 |          380 |          1663 |           |
 mediawiki |  24576 |        6 | f             | t            |           -1 |         11510 |          380 |          1663 |           |
 nowa_baza |  25166 |        6 | f             | t            |           -1 |         11510 |          380 |          1663 |           |
(6 rows)

template1=#

Sam czysty SQL:

$ data=`date +%Y_%m_%d`
$ pg_dump -h localhost -p 5432 -U user -O -b -E UTF8 -Fc -f "db_sql_$data.sql" dbname

Z kompresją:

$ pg_dump -h localhost -p 5432 -U user -O -b -E UTF8 -Z 9 -Fc -f "db_sql_$data.c" "dbname"

Bez danych – sam schemat:

$ pg_dump -h localhost -p 5432 -U user -O -b -E UTF8 -s -Fc -f "db_sql_$data.sql" "dbname"

Same dane – bez schematu:

$ pg_dump -h localhost -p 5432 -U user -O -b -E UTF8 -a -Fc -f "db_sql_$data.sql" "dbname"

Wszystkie bazy:

$ pg_dumpall -h localhost -p 5432 -U mysql -O -b -E UTF8 -f "db_allsql_$data.sql"

Odtwarzanie bazy danych:

psql -h localhost -p 5432 -U user dbname < plik_db_sql.sql

Uwaga: gdy robimy kopię zapasową za pomocą crontab’a to pojawia się problem z hasłem, ponieważ nast nie będzie przy tym, aby można było je wpisać. Aby psql nie pytał o hasło musimy w katalogu domowym użytkownika z jakiego będą wykonywane kopię wpisać do pliku $HOME/.pgpasshasło w postaci:

host:port:dbname:username:password

np:

127.0.0.1:5432:mediawiki:wiki:tajnehaslo111111111