===== PostgreSQL =====

==== Administracja ====


=== Zalogowanie się do PostgreSQL za pomocą psql ===

Logujemy się na root’a:

<file>
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=#
</file>

=== Stworzenie użytkownika na bazie danych ===

<file>
template1=# CREATE USER uzytkownik WITH ENCRYPTED PASSWORD 'nasz_haslo';
CREATE ROLE
template1=#
</file>

=== Zmiana hasła dla użytkownika ===

<file>
template1=# ALTER USER uzytkownik WITH ENCRYPTED PASSWORD 'nowe_pass';
ALTER ROLE
template1=#
</file>

=== Usunięcie użytkownika z bazy danych ===

<file>
template1=# DROP USER uzytkownik;
DROP ROLE
template1=#
</file>

=== Stworzenie instancji na bazie danych ===

<file>
template1=# CREATE DATABASE nowa_baza;
CREATE DATABASE
template1=#
</file>

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

<file>
template1=# CREATE DATABASE nowa_baza WITH OWNER = uzytkownik;
CREATE DATABASE
template1=#
</file>

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

<file>
template1=# CREATE DATABASE nowa_baza WITH OWNER = uzytkownik ENCODING = 'UNICODE';
CREATE DATABASE
template1=#
</file>

=== Usunięcie instancji na bazie danych ===

<file>
template1=# DROP DATABASE nowa_baza;
DROP DATABASE
template1=#
</file>

=== Nadanie praw dla innego użytkownika ===

<file>
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user2;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO user2;

</file>

=== Nadanie praw tylko do odczytu ===

<file>
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
</file>

=== Nadanie praw tylko do backupu ===

<file>
CREATE USER backup_user  WITH ENCRYPTED PASSWORD 'password';
GRANT CONNECT ON DATABASE production to backup_user;
\c production
GRANT USAGE ON SCHEMA public to backup_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
</file>

=== Wyświetlenie tabel w bazie danych ===

<file>
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
...
</file>

=== Wyświetlenie listy użytkowników ===

<file>
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=#
</file>

=== Wyświetlenie listy instancji bazodanowych ===

<file>
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=#
</file>

==== Backup ====

=== Sam czysty SQL ===

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

=== Z kompresją ===

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

=== Bez danych – sam schemat ===

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

=== Same dane – bez schematu ===

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

=== Wszystkie bazy ===

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

=== Odtwarzanie bazy danych ===

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

=== Crontab ===

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:

<file>
host:port:dbname:username:password
</file>

np:

<file>
127.0.0.1:5432:mediawiki:wiki:tajnehaslo111111111
</file>

==== Użytkowanie ====

=== Zalogowanie się do PostgreSQL za pomocą psql ===

<file>
$ psql -h localhost -p 15432 -U mediawiki mediawiki
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

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

mediawiki=>
</file>

=== Pokazanie struktury bazy danych ===

<file>
mediawiki=> use mediawiki
mediawiki-> \d
                    List of relations
  Schema   |         Name          |   Type   |   Owner
-----------+-----------------------+----------+-----------
 mediawiki | archive               | table    | mediawiki
 mediawiki | category              | table    | mediawiki
 mediawiki | category_id_seq       | sequence | mediawiki
 mediawiki | categorylinks         | table    | mediawiki
.
.
.
 mediawiki | user_groups           | table    | mediawiki
 mediawiki | user_newtalk          | table    | mediawiki
 mediawiki | user_user_id_seq      | sequence | mediawiki
 mediawiki | watchlist             | table    | mediawiki
(51 rows)

mediawiki->
</file>

=== Pokazanie struktury tabeli ===

<file>
mediawiki-> \d archive
                   Table "mediawiki.archive"
    Column     |           Type           |     Modifiers
---------------+--------------------------+--------------------
 ar_namespace  | smallint                 | not null
 ar_title      | text                     | not null
 ar_text       | text                     |
 ar_page_id    | integer                  |
 ar_parent_id  | integer                  |
 ar_comment    | text                     |
 ar_user       | integer                  |
 ar_user_text  | text                     | not null
 ar_timestamp  | timestamp with time zone | not null
 ar_minor_edit | smallint                 | not null default 0
 ar_flags      | text                     |
 ar_rev_id     | integer                  |
 ar_text_id    | integer                  |
 ar_deleted    | smallint                 | not null default 0
 ar_len        | integer                  |
Indexes:
    "archive_name_title_timestamp" btree (ar_namespace, ar_title, ar_timestamp)
    "archive_user_text" btree (ar_user_text)
Foreign-key constraints:
    "archive_ar_user_fkey" FOREIGN KEY (ar_user) REFERENCES mwuser(user_id) ON DELETE SET NULL

mediawiki->
</file>