Table of contents generated with markdown-toc
- This will list all oprions for
psql
psql --help
-
For
default username
check theConnection options
output section of thepsql --help
command -
start postgres service
sudo service postgresql restart
brew install postgresql
check installation path of postgres with which postgres
brew uninstall postgresql
rm -fr <put the installation path here>
brew services list
restart postgres service
brew services restart postgresql
manual start for mac os
pg_ctl -D /usr/local/var/postgres start
automatic start for mac os
brew services start postgresql
- stop postgres service for mac os
pg_ctl -D /usr/local/var/postgres stop
- login as default user
sudo -u postgres psql postgres
MacOS
- login as default user(i.e postgres)
psql postgres
- check postgres logs for version > 9.6
/usr/local/var/log/postgres.log
- login as specific user
sudo -u username psql postgres
or
sudo -u username psql your_dbname
- check the current user and db connection details In the postgres cli, run
\conninfo
- create user From Terminal
sudo -u postgres createuser username_here;
From psql console
CREATE USER username WITH PASSWORD "password";
- change password
ALTER USER username_here WITH PASSWORD 'password';
- change role to superuser
ALTER USER username_here WITH superuser;
\du
CREATE USER username WITH PASSWORD 'password';
CREATE ROLE rolename;
ALTER USER username WITH rolename;
In the postgres console(psql), run
\?
Check the logged in user and connection details with
\conninfo
the database will be created under the current user.
To create database,
CREATE DATABASE database_name;
- see the list of databases In postgres console(psql)
\l
- connect to a database In postgres console(psql)
\c database_name
- list tables of the public schema In postgres console(psql)
\dt
- list tables of certain schema, e.g.,
\dt public.*
In postgres console(psql)
\dt <schema-name>.*
- list tables of all schemas In postgres console(psql)
\dt *.*
pg_dump -h <endpoint> -U postgres dbname=your_db_name -f file.sql
pg_dump -h <endpoint> -U <username> -d <database name> > file.psql
pg_dump -h <endpoint of instance> -U <master username> -d <database name> > file.dump
pg_dump -U username dbname > dumpfilename.pgsql
pg_dump --format=c --compress=9 --no-acl --no-owner -h HOST_HERE -U postgres DB_NAME_HERE > filename.dump
- This dump format will be restored using
pg_restore
The restore command is
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U USERNAME_HERE -d DB_NAME_HERE dump_file.dump
pg_restore --verbose --clean --no-acl --no-owner -h <RDS Database Endpoint> -U <Postgres DB Username> -d <Postgres DB Name> /path/to/file.dump
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U <Postgres DB Username> -d db_name /path/to/file.dump
psql -f file.sql --host <endpoint> --port 5432 --username <database username> --dbname <database name>
If you try to restore a dumpfile with pg_restore
but it says that its a custom dump format and use psql
to restore it then do the following:
psql -f file.extension --host <endpoint> --port 5432 --username <database username> --dbname <database name>
pg_restore --verbose --clean --no-acl --no-owner -h <endpoint> -U <database username> -d <database name> file.psql
psql -U postgres -p 5432 -h alisuq-production-database.c2m9bh3bioit.me-south-1.rds.amazonaws.com -d alisuq_production_db < file.dump
gunzip -c filename.gz | psql dbname
heroku pg:psql -a app_name < file.sql
👍