#install
sudo apt-get install postgresql
sudo apt-get install libpq-dev
# for hstore
sudo apt-get install postgresql-contrib
##Modify /etc/postgresql/9.3/main/pg_hba.conf access control, local TCP access set to full trust
#host all all 127.0.0.1/32 md5
host all all 127.0.0.1/32 trust
#service
sudo service postgresql restart
#import
psql -U system db_name < 20151130_db_name.pgsql
#export
pg_dump -u system db_name > 20151130_db_name.pgsql
#login
psql -U username db_name
#show db/schema
\dt *.*
\dn+
\list
alter schema public rename to public_save;
#create new schema as default schema:
create schema public;
pg_restore -f pub.backup db_temp [and whatever other options]
alter schema public rename to temp_schema;
alter schema public_save rename to public;
#drop
DROP SCHEMA IF EXISTS schema_name;
DROP SCHEMA IF EXISTS schema_name CASCADE;
sudo -u postgres psql
-> postgres=#
CREATE DATABASE test_database;
CREATE USER test_user WITH password 'qwerty';
GRANT ALL privileges ON DATABASE test_database TO test_user;
ALTER USER test_user CREATEDB;
DROP ROLE test_user;
psql -h localhost test_database test_user
\?
, \h
- available psql commands
\d users
, \du
, \dg
- List of roles - description of the 'users' table
\d
, \dt
- all tables
\list
- all DBs
\a
###Backup
Uncompressed:
$ pg_dump -h IP_ADDRESS -p 5432 -U app -N postgis -N topology -d DATABASE_NAME > your_file_name.sql
Compressed:
$ pg_dump -h IP_ADDRESS -p 5432 -U app -a -N postgis -N topology -Fc -d DATABASE_NAME > your_file_name.dump
The variables
IP_ADDRESS = The IP address of your database server
DATABASE_NAME = The database name of your server (found on the Database tab of your app)
The flags
-h = Host
-p = Port
-U = User
-d = Database name
-N = Exclude schema (in particular, exclude the PostGIS and topology schema if you aren’t using any of their geographic functionality)
-Fc = Format compressed
Optional flags
-a = Data only
-c = Clean
psql -h IP_ADDRESS -p 5432 -U app -d databasename -f your_file_name.sql
Connect to the database, the default user and database postgres
psql -U user -d dbname
Switch databases, the equivalent of mysql use dbname
\c dbname
List database, the equivalent of mysql show databases
\l
Include tables, equivalent to the mysql show tables
\dt
View table structure, equivalent desc tablename, show columns from tbname
\d tblname
Remove database
DROP DATABASE pg_database_3;
copy bitauth_userdata from '/home/chideo/db_backup/csv/userdata.csv';
Export mysql to csv
mysql -A service_db -h 199.115.165.194 -u chadmin -pch@dmin! -ss -e "SELECT * from bitauth_userdata;" > userdata.csv