Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

https://stackoverflow.com/questions/27037990/connecting-to-postgres-via-database-url-and-unix-socket-in-rails/28125660

https://stackoverflow.com/questions/2732474/restore-a-postgres-backup-file-using-the-command-line

Dump/Restore DB:

psql -U <username> -d <dbname> -1 -f <filename>.pgdump
pg_restore -U <username> -d <dbname> -1 <filename>.pgdump

Connect to DB:

\c DBNAME
postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]`
The URI scheme designator can be either postgresql:// or postgres://. Each of the URI parts is optional. The following examples illustrate valid URI syntax uses:

postgresql://
postgresql://localhost
postgresql://localhost:5433
postgresql://localhost/mydb 
postgresql://user@localhost
postgresql://user:secret@localhost
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp
Components of the hierarchical part of the URI can also be given as parameters. For example:

postgresql:///mydb?host=localhost&port=5433

Test if Database Socket is Open and Listening:

https://www.postgresql.org/docs/9.3/app-pg-isready.html

pg_isready --dbame= --host

postgres@openproject:~$ pg_isready --dbname=openproject --host=localhost
localhost:5432 - accepting connections

Test DB Access:

https://www.postgresql.org/docs/8.1/app-psql.html

su - postgres

postgres@openproject:~$ psql --username openproject --password --dbname openproject
Password for user openproject:

Authentication (pg_hba.conf):

https://www.postgresql.org/docs/9.3/auth-pg-hba-conf.html

systemctl stop postgresql && nano /etc/postgresql/10/main/pg_hba.conf && systemctl start postgresql && systemctl status postgresql

Find Version:

Server version:

pg_config --version

Client version:

psql --version

Show DB's:

\l

Show Tables:

\c database_name
\dt

Create Database and User:

sudo -u postgres psql

CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
  • No labels