3/20/2017 - 5:24 PM

Essential commands to configure postgres

Essential commands to configure postgres

# Connect to a database with a username
psql <database_name> -U <user_name>

# List all databases

# Connect to a database inside psql
\connect <database_name>

# List all tables in a database
\dt *.*

# Change username's password from psql# prompt after logging in
ALTER USER "<user_name>" WITH PASSWORD '<new_password>';

# Configure a jdbc connection from rubymine. Note: jdbc requires password
Host: localhost
Database: <database_name>
User: <user_name>
Password: <password>
Port: 5432

# dump a database
pg_dump -h localhost -U postgres <database-name> > dumpfile

# dump a table
pg_dump -h localhost -U postgres <database-name> -t <table-name> > dumpfile-table

# to login an rds instance in amazon postgres from command line
psql -h <end-point> -U <username> -d <database>

# to reconstruct aws rds from a dump file in localhost
psql -h <end-point> -U <username> -d <database> < dumpfile-table

# to create a fake database called 'fakelocal' with 200 rows in a table named 'company' in localhost, use fake2db tool
fake2db --rows 200 --db postgresql --name fakelocal --host localhost --port 5432 --username postgres --password password