Create User
|
1 |
create user username with password 'userpassword'; |
Change user password
|
1 |
ALTER USER Postgres WITH PASSWORD 'mypass'; |
Create Database
|
1 |
create database mydatabse owner username; |
Privileges
|
1 |
alter role username superuser createrole createdb replication; |
Change database owner
|
1 |
alter database database_name owner to new_owner; |
Copy a database
|
1 |
CREATE DATABASE newdb WITH TEMPLATE originaldb; |
Dump
|
1 |
sudo -u postgres pg_dump database_name > database_name.sql |
|
1 2 3 |
cd /home/deployer/apps/project/current && PGPASSWORD='MyPass' pg_dump --no-acl --no-owner -U deployer -h localhost database_name | bzip2 - - > db/database_name.sql.bz2 |
Restore
|
1 |
sudo -u postgres psql database_name < database_name.sql |
p.s.
in Ubuntu default username database PostGres is postgres, but in FreeBSD this is pgsql
Postgres commands
|
1 2 3 4 5 6 7 |
\connect databasename or \c databasename: switch databasename \list or \l: list all databases \dt: list all tables in the current database \d+ <table_name>: list table fields \dn: list all Schemas \du: list all users |