Posted   by 

Cheat Sheet Postgresql



A quick reference to PostgreSQL.

Introduction to PostgreSQL Cheat Sheet. PostgreSQL cheat sheet is defined as how to use the basic commands of PostgreSQL, we have created our basic cheat sheet of PostgreSQL to take a quick overview of the PostgreSQL database. Postgres Cheatsheet created by your friends at Timescale. Postgresql Cheat Sheet PostgreSQL also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. Table of contents.

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:

9.5

To remove an extension:

To connect to remote psql host 1:

To backup a psql database without typing password 2: Sophos ap100x.

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:

to export a table to a csv 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:

Postgresql

The output would show something like this:

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

Regex Cheat Sheet Postgresql

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/

Sheet

Now connect to the remote server:

Postgresql Cheat Sheet Pentestmonkey

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

Postgresql Sql Injection Cheat Sheet

1
2
3