A quick reference to PostgreSQL.

1. Postgresql Command Line Cheat Sheet Download
2. Postgresql Command Line Cheat Sheet Example

Created on: 2019-01-22

Tag: cheat_sheet

PostgreSQL Cheat Sheet CREATE DATABASE. Backup all databases (command line) pgdumpall pgbackup.sql Run a SQL script (command line) psql -f script.sql databaseName. PostgreSQL Command Line Cheat Sheet 02 Oct 2012 » » postgresql, command line. Share this on → Tweet « Testing Mail From the Command Line on Ubuntu Bashprofile. Change database. Use c to change database names: mary= c marydb.

But we still can forget a simple command to create user or show tables list. Also we can get stuck when switching from MySQL to PostgreSQL or whatever else, cause command syntax is different. For this issue I lead a special cheatsheet table of DBMS commands.

To check postgresql version:

source: https://stackoverflow.com/a/13733856

To create a super suer with passowrd:

To create a database on Postgresql:

To remove an extension:

To connect to remote psql host 1:

To backup a psql database without typing password 2:

To create database from sql file:

to backup a single table:

to export a sql statement output to a csv file with header:

to export a sql statement output to a geojson file:

COPY current_relation_members TO '/var/lib/postgresql/csv/current_relation_members.CSV' DELIMITER ',' CSV HEADER;

The following are the Meta-Commands for psql command. This can be used with either with -c flag of the psql command like:

Or within the interactive prompt that comes after sudo -u postgres psql command. There are many Meta-Commands and there are available in the psql document. Bellow are a few useful most useful for me:

• connect to a database: c $DATEBASE_NAME

• list all users: du

• list all database: l or list

• list all table: dt

To allow remote connections to PostgreSQL database server, first check listen_addresses in postgresql.conf:

The output would show something like this:

Now let's edit the postgresql.conf file in our editor of choice:

Search for listen_addresses, and set it to '*' for all addresses or comma separated IP address, save the file and exit. Now we need to change the pg_hba.conf file so let's open it with our editor:

More details here PostgreSQL: Documentation: Connections and Authentication.

Now add the following to the end of file:

Now save the file and exit. Now restart PostgreSQL:

OR:

source: https://bosnadev.com/2015/12/15/allow-remote-connections-postgresql-database-server/

Now connect to the remote server:

source: https://askubuntu.com/a/423181

A quick reference to PostgreSQL.

Created on: 2019-01-22

Tag: cheat_sheet

To check postgresql version:

source: https://stackoverflow.com/a/13733856

To create a super suer with passowrd:

To create a database on Postgresql:

To remove an extension:

To connect to remote psql host 1:

To backup a psql database without typing password 2:

To create database from sql file:

to backup a single table:

to export a sql statement output to a csv file with header:

to export a sql statement output to a geojson file:

COPY current_relation_members TO '/var/lib/postgresql/csv/current_relation_members.CSV' DELIMITER ',' CSV HEADER;

The following are the Meta-Commands for psql command. This can be used with either with -c flag of the psql command like:

Or within the interactive prompt that comes after sudo -u postgres psql command. There are many Meta-Commands and there are available in the psql document. Bellow are a few useful most useful for me:

• connect to a database: c $DATEBASE_NAME

• list all users: du

• list all database: l or list

• list all table: dt

To allow remote connections to PostgreSQL database server, first check listen_addresses in postgresql.conf:

The output would show something like this:

Now let's edit the postgresql.conf file in our editor of choice:

Search for listen_addresses, and set it to '*' for all addresses or comma separated IP address, save the file and exit. Now we need to change the pg_hba.conf file so let's open it with our editor:

Postgresql Command Line Cheat Sheet Download

Postgresql Command Line Cheat Sheet Example

More details here PostgreSQL: Documentation: Connections and Authentication.

Now add the following to the end of file:

Now save the file and exit. Now restart PostgreSQL:

OR:

source: https://bosnadev.com/2015/12/15/allow-remote-connections-postgresql-database-server/

Now connect to the remote server:

source: https://askubuntu.com/a/423181