kamil.orchia.pl

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

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

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

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

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

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

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

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

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

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

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;

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

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

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

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

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

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

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

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

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

$ 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=>

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

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