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