Postgres Database Get Started Commands


Login to postgres:

sudo -i -u postgres
psql

switch to database
\c dbname;

Create Database:-

create database dbname;

Create User:-

create user username with password 'password';

Give previlleges:-

GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO username;

Import database:-

psql dbname < /home/13082014_daily_extra.sql;

Export database:-

pg_dump dbname > /var/www/html/dbname.sql;

list databases
\list;

list tables

\dt;

Postgres default port is 5432

Check postgres port
pg_lsclusters
I have machines with multiple postgres instances running -- and so I also have the issue of trying to match up the correct database with each port. I tend to do:

$ ps aux | grep postgres | grep -v 'postgres:'

And then, for each of instances returned look for the directory (-D argument) and:

$ sudo grep port $DIR/postgresql.conf

No comments

Enter your email address:

Delivered by FeedBurner

OR

 Subscribe in a reader

 
Latest Blogs
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Tips for optimizing php code by PHP Expert Important SERVER Variables in PHP - By PHP Expert Improved Error Messages in PHP 5 - By PHP Expert New Object Oriented Features - By PHP Expert New Object Oriented Features - By PHP Expert New Object Oriented Features - By PHP Expert New Object Oriented Features - By PHP Expert New Object Oriented Features - By PHP Expert New Object Oriented Features - By PHP Expert New Object Oriented Features - By PHP Expert Object Overloading in PHP 5 Persistable Classes - By PHP Expert Dynamic Getter/Setter Methods - By PHP Expert New Functions in PHP 5 New Directives - By PHP Expert Exception Handling - By PHP Expert Password Encryption in PHP - By PHP Expert Output Buffering in PHP - By PHP Expert Page Excerpts Using CURL - By PHP Expert Quick and Easy Google Site Search - By PHP Expert Always Be Notified When Google Crawls Your Site - By PHP Expert How to POST Form Data using CURL - By PHP Expert Cryptography for web developers - By PHP Expert Dynamically Loading JavaScript Files - by PHP Expert What is Web 2.0 - By PHP Expert