29-04-2021

Cheat Sheet Postgresql



A quick reference to PostgreSQL.

Sql Injection Cheat Sheet Postgresql

  1. Introduction to the PostgreSQL cheat sheet The PostgreSQL offers scaling, storage flexibility, and ease of database management for developers, DBAs, and other technical professionals. Because of PostgreSQL’s ability to offer architecture stability along with an extensive coding capability, the plethora of commands and statements are endless.
  2. At this point you’re expected to type commands and parameters into the command line. Psql vs SQL commands. Psql has two different kinds of commands. Those starting with a backslash are for psql itself, as illustrated by the use of q to quit.

Created on: 2019-01-22

Tag: cheat_sheet

To check postgresql version:

Postgres Cheatsheet This is a collection of the most common commands I run while administering Postgres databases. The variables shown between the open and closed tags, ', should be replaced with a name you choose. Postgres has multiple shortcut functions, starting with a forward slash, '. Login to postgresql psql -U postgres Some interesting flags (to see all, use -h or -help depending on your psql version): -E: will describe the underlaying queries of the commands (cool for learning!) -l: psql will list all databases and then exit (useful if the user you connect with doesn’t has a default database, like at AWS RDS) Most d commands support.

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

To create a super suer with passowrd:

PostgresqlCheat sheet postgresql pdfCheat

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:

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:

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:

Postgresql cheat sheet pdf

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:

Postgres Command Line

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

Postgresql Command Line Cheat Sheet

1
2
3