Postgres

Índice

1. Instalação

1.1 Postgres
1.2 pgAdmin 4

2. Manage

3. Backup and Restore

3.1 Backup
3.2 Restore

4. Commands

1. Instalação

1.1 Postgres

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

1.2 pgAdmin 4

Install dependencies, create a virtual environment, download, install & configure

sudo apt-get install virtualenv python-pip libpq-dev python-dev

virtualenv pgadmin4
cd pgadmin4
source bin/activate

pip install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v1.6/pip/pgadmin4-1.6-py2.py3-none-any.whl

Configure

Write the SERVER_MODE = False in lib/python2.7/site-packages/pgadmin4/config_local.py to configure to run in single-user mode:

echo "SERVER_MODE = False" >> lib/python2.7/site-packages/pgadmin4/config_local.py

Run

python lib/python2.7/site-packages/pgadmin4/pgAdmin4.py

Access

http://localhost:5050

2. Manage

2.1 Switching Over to the postgres Account

The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, we can log into that account.

Switch over to the postgres account on your server by typing:

$ sudo -i -u postgres

You can now access a Postgres prompt immediately by typing:

$ psql

You will be logged in and able to interact with the database management system right away.

Exit out of the PostgreSQL prompt by typing:

postgres=# \q

3. Backup and Restore

3.1 Backup

pg_dump -U username -f backup.sql database_name

3.2 Restore

psql -d database_name -f backup.sql

4. Commands

4.1 List Databases

postgres=# \l

4.2 List Table

postgres=# \d

4.3 Connect to a Database

postgres=# \c database_name

4.4 Create Database

postgres=# create database dbname;

4.5 Create Table

postgres=# create table table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

4.6 Drop Database

postgres=# drop database trezentos;

4.7 Drop Table

postgres=# drop table user, company;

4.8 Exit out

postgres=# \q