common postgresql command

postgresql_cmd.md

#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 

rename default schema:

alter schema public rename to public_save;

#create new schema as default schema:

create schema public;

restore data

pg_restore -f pub.backup db_temp [and whatever other options]

rename schemas according to need:

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;

PSQL -- PostgreSQL interactive terminal

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

PSQL Commands

\?, \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

import

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