Rescue Commands in Postgresql

gistfile1.txt

Rescue Commands in Postgresql 

#start with:
$sudo su postgres
$psql

#then do :

#show all databases
\dl

#show roles
\du

################## Creating/removing databases ###########################

#create a database with default template 0

#examples wtih latin encoding template 0:
create database my_database encoding 'latin1' template template0;

#show all collation lists
select * from pg_collation;

#create database with template parameter and collation:
create database my_database template template0 encoding 'utf8' lc_collate 'el_gr.utf-8' lc_ctype 'el_gr.utf-8';

#Create databases with UTF8 default and en_US collation. The standard way
create database my_database encoding 'utf8';

#drop database:
drop database my_database;

################## Creating/granting permissions permissions ############

#create a user called mike with a simple password
create user mike with password 'jw8s0f4';

#grant privileges to database:
grant all privileges on database my_database to mike;
#drop privileges
revoke all privileges on database my_database from mike;

############### Change password on user #################################

ALTER USER mike WITH PASSWORD 'new_password';

################## Creating database dumps ###############################

#create a folder postgres has access to and cd into it
mkdir /home/user/postgres_backup
chown -R postgres:postgres /home/user/postgres_backup
$sudo su postgres
$pg_dump my_database -f /home/user/backups/my_database.dump

################# Importing/Restoring database dumps ###############################

#cd into to the folder of the database backup:

#restore
cd /home/user/backups/
$sudo su postgres
$psql my_database < my_database.dump

#backup:

################# Connecting to Database ###################################

Postgres was freaking me out with restoring a database back with permission:

Solution:
Did backup on database on different server and that has different role than I need, with this command:

pg_dump -Fc db_name -f db_name.dump

Then I copied backup to another server where I need to restore the database, but there is no such owner 
that was used for that database. 

Let say database has owner owner1, but on different server I only have owner2 
and I need to restore that database and change owner.

What I did on another server when restoring:

createdb   -p 5433 -T template0 db_name 
pg_restore -p 5433 --role=owner2 -d db_name db_name.dump

pg_restore -c --no-owner --role=demouser -d locklizard_development locklizard_development.dump


https://www.postgresql.org/docs/9.0/static/auth-methods.html
The ident authentication method works by obtaining the client's operating 
system user name and using it as the allowed database user name (with an optional user name mapping). 
The determination of the client's user name is the security-critical point, 
and it works differently depending on the connection type, as described below.

References:
http://www.vertabelo.com/blog/technical-articles/collations-in-postgresql