postgresql

Super simple. A quick sudo apt-get install postgresql will get you going. This will create a postgres user and automatically create a new cluster, so you won’t need to do an initdb.

To run administration commands on your server, you’ll need to log in as the postgres user.
Start by setting a password for this user:

sudo passwd postgres

Now you can do a

su - postgres

to become this user and You can make sure this is working by typing

psql postgres

and confirming that the prompt looks like this:

'postgres=#’,

which indicates you’re a superuser.

Creating a user and a database

CREATE USER sonar WITH PASSWORD 'sonar';
CREATE DATABASE sonar WITH OWNER sonar ENCODING 'UTF8';

Let’s assume we have a typical scenario: a Rails application which needs a development and test DB. We’ll also want a user specifically for this application. I’ll explain how this would be done in MySQL, for comparison, and then PostgreSQL.

Let’s do this in PostgreSQL. We’ll be using the terminal commands, so this will need to be done as your postgres superuser (on OSX, this might just be your normal user; on Linux, it’s probably the postgres user):

$ createuser rails --pwprompt
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

$ createdb -O rails blog_dev

Now we’ve created a user ('rails’) who is not a superuser, but does have permission to create databases. This user will be allowed to drop and create its own databases (a similar set of permissions to our MySQL example above).
We then use the createdb command, creating a new database called blog and setting its owner to rails.

You’ll now be able to use psql to log in as the new user and create another database:

$ psql postgres rails

postgres=> CREATE DATABASE blog_test; CREATE DATABASE

\a	Output format aligned/unaligned
\c (connect)	[ -reuse-previous=on|off ] [ dbname [ username ] [ host ] [ port ] | conninfo ]
\cd	change current working directory
\conninfo	Outputs information about the current database connection.
\copy	{ table [ ( column_list ) ] | ( query ) } { from | to } { 'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [ [ with ] ( option [, ...] ) ]
\d+	List of relations
\da	List of aggregate functions
\db+	List of tablespaces
\dc	List of conversions
\dD	List of domains
\dd	Object descriptions
\ddp	Default access privileges
\df	list of functions
\dp	Access privileges
\drds	[ role-pattern [ database-pattern ] ] Lists defined configuration settings. These settings can be role-specific, database-specific, or both.
\dT[S+]	[ pattern ] Lists data types. If pattern is specified, only types whose names match the pattern are listed. If + is appended to the command name, each type is listed with its internal name and size, its allowed values if it is an enum type, and its associated permissions. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects
\du	To get a list of roles
\dx[+]	List of installed extensions
\dy[+]	List of event triggers
\e \edit	[ filename ] [ line_number ] If filename is specified, the file is edited; after the editor exits, its content is copied back to the query buffer. If no filename is given, the current query buffer is copied to a temporary file which is then edited in the same fashion.
\echo text [ ... ]	Prints the arguments to the standard output, separated by one space and followed by a newline. This can be useful to intersperse information in the output of scripts. For example: \echo `date` Tue Oct 26 21:40:57 CEST 1999
\ef	[ function_description [ line_number ] ] This command fetches and edits the definition of the named function, in the form of a CREATE OR REPLACE FUNCTION command. Editing is done in the same way as for \edit. After the editor exits, the updated command waits in the query buffer; type semicolon or \g to send it, or \r to cancel.
\f [ string ]	Sets the field separator for unaligned query output. The default is the vertical bar (|). See also \pset for a generic way of setting output options.
\g [ filename ]
\g [ |command ]	Sends the current query input buffer to the server, and optionally stores the query's output in filename or pipes the output to the shell command command. The file or command is written to only if the query successfully returns zero or more tuples, not if the query fails or is a non-data-returning SQL command. A bare \g is essentially equivalent to a semicolon. A \g with argument is a "one-shot" alternative to the \o command.
\gset [ prefix ]	Sends the current query input buffer to the server and stores the query's output into psql variables (see Variables). The query to be executed must return exactly one row. Each column of the row is stored into a separate variable, named the same as the column. For example:
	=> SELECT 'hello' AS var1, 10 AS var2
	-> \gset
	=> \echo :var1 :var2
	hello 10
	If you specify a prefix, that string is prepended to the query's column names to create the variable names to use:
	=> SELECT 'hello' AS var1, 10 AS var2
	-> \gset result_
	=> \echo :result_var1 :result_var2
	hello 10
	If a column result is NULL, the corresponding variable is unset rather than being set.
	If the query fails or does not return one row, no variables are changed.
\h or \help [ command ]	Gives syntax help on the specified SQL command. If command is not specified, then psql will list all the commands for which syntax help is available. If command is an asterisk (*), then syntax help on all SQL commands is shown.
	Note: To simplify typing, commands that consists of several words do not have to be quoted. Thus it is fine to type \help alter table.
\H or \html	Turns on HTML query output format. If the HTML format is already on, it is switched back to the default aligned text format. This command is for compatibility and convenience, but see \pset about setting other output options.
\i or \include filename	Reads input from the file filename and executes it as though it had been typed on the keyboard.
	If filename is - (hyphen), then standard input is read until an EOF indication or \q meta-command. This can be used to intersperse interactive input with input from files. Note that Readline behavior will be used only if it is active at the outermost level.
	Note: If you want to see the lines on the screen as they are read you must set the variable ECHO to all.
\ir or \include_relative filename	The \ir command is similar to \i, but resolves relative file names differently. When executing in interactive mode, the two commands behave identically. However, when invoked from a script, \ir interprets file names relative to the directory in which the script is located, rather than the current working directory.
\l[+] or \list[+] [ pattern ]	List the databases in the server and show their names, owners, character set encodings, and access privileges. If pattern is specified, only databases whose names match the pattern are listed. If + is appended to the command name, database sizes, default tablespaces, and descriptions are also displayed. (Size information is only available for databases that the current user can connect to.)
\lo_export loid filename	Reads the large object with OID loid from the database and writes it to filename. Note that this is subtly different from the server function lo_export, which acts with the permissions of the user that the database server runs as and on the server's file system.
	Tip: Use \lo_list to find out the large object's OID.
\lo_import filename [ comment ]	Stores the file into a PostgreSQL large object. Optionally, it associates the given comment with the object. Example:
	foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
	lo_import 152801
	The response indicates that the large object received object ID 152801, which can be used to access the newly-created large object in the future. For the sake of readability, it is recommended to always associate a human-readable comment with every object. Both OIDs and comments can be viewed with the \lo_list command.
	Note that this command is subtly different from the server-side lo_import because it acts as the local user on the local file system, rather than the server's user and file system.
\lo_list	Shows a list of all PostgreSQL large objects currently stored in the database, along with any comments provided for them.
\lo_unlink loid	Deletes the large object with OID loid from the database.
	Tip: Use \lo_list to find out the large object's OID.
\o or \out [ filename ]
\o or \out [ |command ]	Arranges to save future query results to the file filename or pipe future results to the shell command command. If no argument is specified, the query output is reset to the standard output.
	"Query results" includes all tables, command responses, and notices obtained from the database server, as well as output of various backslash commands that query the database (such as \d), but not error messages.
	Tip: To intersperse text output in between query results, use \qecho.
\p or \print	Print the current query buffer to the standard output.
\password [ username ]	Changes the password of the specified user (by default, the current user). This command prompts for the new password, encrypts it, and sends it to the server as an ALTER ROLE command. This makes sure that the new password does not appear in cleartext in the command history, the server log, or elsewhere.
\prompt [ text ] name	Prompts the user to supply text, which is assigned to the variable name. An optional prompt string, text, can be specified. (For multiword prompts, surround the text with single quotes.)
	By default, \prompt uses the terminal for input and output. However, if the -f command line switch was used, \prompt uses standard input and standard output.
\pset [ option [ value ] ]	This command sets options affecting the output of query result tables. option indicates which option is to be set. The semantics of value vary depending on the selected option. For some options, omitting value causes the option to be toggled or unset, as described under the particular option. If no such behavior is mentioned, then omitting value just results in the current setting being displayed.
	\pset without any arguments displays the current status of all printing options.
	Adjustable printing options are:
	border
	The value must be a number. In general, the higher the number the more borders and lines the tables will have, but details depend on the particular format. In HTML format, this will translate directly into the border=... attribute. In most other formats only values 0 (no border), 1 (internal dividing lines), and 2 (table frame) make sense, and values above 2 will be treated the same as border = 2. The latex and latex-longtable formats additionally allow a value of 3 to add dividing lines between data rows.
	columns
	Sets the target width for the wrapped format, and also the width limit for determining whether output is wide enough to require the pager or switch to the vertical display in expanded auto mode. Zero (the default) causes the target width to be controlled by the environment variable COLUMNS, or the detected screen width if COLUMNS is not set. In addition, if columns is zero then the wrapped format only affects screen output. If columns is nonzero then file and pipe output is wrapped to that width as well.
	expanded (or x)
	If value is specified it must be either on or off, which will enable or disable expanded mode, or auto. If value is omitted the command toggles between the on and off settings. When expanded mode is enabled, query results are displayed in two columns, with the column name on the left and the data on the right. This mode is useful if the data wouldn't fit on the screen in the normal "horizontal" mode. In the auto setting, the expanded mode is used whenever the query output is wider than the screen, otherwise the regular mode is used. The auto setting is only effective in the aligned and wrapped formats. In other formats, it always behaves as if the expanded mode is off.
	fieldsep
	Specifies the field separator to be used in unaligned output format. That way one can create, for example, tab- or comma-separated output, which other programs might prefer. To set a tab as field separator, type \pset fieldsep '\t'. The default field separator is '|' (a vertical bar).
	fieldsep_zero
	Sets the field separator to use in unaligned output format to a zero byte.
	footer
	If value is specified it must be either on or off which will enable or disable display of the table footer (the (n rows) count). If value is omitted the command toggles footer display on or off.
	format
	Sets the output format to one of unaligned, aligned, wrapped, html, asciidoc, latex (uses tabular), latex-longtable, or troff-ms. Unique abbreviations are allowed. (That would mean one letter is enough.)unaligned format writes all columns of a row on one line, separated by the currently active field separator. This is useful for creating output that might be intended to be read in by other programs (for example, tab-separated or comma-separated format).
	aligned format is the standard, human-readable, nicely formatted text output; this is the default.
	wrapped format is like aligned but wraps wide data values across lines to make the output fit in the target column width. The target width is determined as described under the columns option. Note that psql will not attempt to wrap column header titles; therefore, wrapped format behaves the same as aligned if the total width needed for column headers exceeds the target.
	The html, asciidoc, latex, latex-longtable, and troff-ms formats put out tables that are intended to be included in documents using the respective mark-up language. They are not complete documents! This might not be necessary in HTML, but in LaTeX you must have a complete document wrapper. latex-longtable also requires the LaTeX longtable and booktabs packages.
	linestyle
	Sets the border line drawing style to one of ascii, old-ascii or unicode. Unique abbreviations are allowed. (That would mean one letter is enough.) The default setting is ascii. This option only affects the aligned and wrapped output formats.
	ascii style uses plain ASCII characters. Newlines in data are shown using a + symbol in the right-hand margin. When the wrapped format wraps data from one line to the next without a newline character, a dot (.) is shown in the right-hand margin of the first line, and again in the left-hand margin of the following line.
	old-ascii style uses plain ASCII characters, using the formatting style used in PostgreSQL 8.4 and earlier. Newlines in data are shown using a : symbol in place of the left-hand column separator. When the data is wrapped from one line to the next without a newline character, a ; symbol is used in place of the left-hand column separator.
	unicode style uses Unicode box-drawing characters. Newlines in data are shown using a carriage return symbol in the right-hand margin. When the data is wrapped from one line to the next without a newline character, an ellipsis symbol is shown in the right-hand margin of the first line, and again in the left-hand margin of the following line.
	When the border setting is greater than zero, the linestyle option also determines the characters with which the border lines are drawn. Plain ASCII characters work everywhere, but Unicode characters look nicer on displays that recognize them.
	null
	Sets the string to be printed in place of a null value. The default is to print nothing, which can easily be mistaken for an empty string. For example, one might prefer \pset null '(null)'.
	numericlocale
	If value is specified it must be either on or off which will enable or disable display of a locale-specific character to separate groups of digits to the left of the decimal marker. If value is omitted the command toggles between regular and locale-specific numeric output.
	pager
	Controls use of a pager program for query and psql help output. If the environment variable PAGER is set, the output is piped to the specified program. Otherwise a platform-dependent default (such as more) is used.
	When the pager option is off, the pager program is not used. When the pager option is on, the pager is used when appropriate, i.e., when the output is to a terminal and will not fit on the screen. The pager option can also be set to always, which causes the pager to be used for all terminal output regardless of whether it fits on the screen. \pset pager without a value toggles pager use on and off.
	pager_min_lines
	If pager_min_lines is set to a number greater than the page height, the pager program will not be called unless there are at least this many lines of output to show. The default setting is 0.
	recordsep
	Specifies the record (line) separator to use in unaligned output format. The default is a newline character.
	recordsep_zero
	Sets the record separator to use in unaligned output format to a zero byte.
	tableattr (or T)
	In HTML format, this specifies attributes to be placed inside the table tag. This could for example be cellpadding or bgcolor. Note that you probably don't want to specify border here, as that is already taken care of by \pset border. If no value is given, the table attributes are unset.
	In latex-longtable format, this controls the proportional width of each column containing a left-aligned data type. It is specified as a whitespace-separated list of values, e.g. '0.2 0.2 0.6'. Unspecified output columns use the last specified value.
	title
	Sets the table title for any subsequently printed tables. This can be used to give your output descriptive tags. If no value is given, the title is unset.
	tuples_only (or t)
	If value is specified it must be either on or off which will enable or disable tuples-only mode. If value is omitted the command toggles between regular and tuples-only output. Regular output includes extra information such as column headers, titles, and various footers. In tuples-only mode, only actual table data is shown.
	unicode_border_linestyle
	Sets the border drawing style for the unicode line style to one of single or double.
	unicode_column_linestyle
	Sets the column drawing style for the unicode line style to one of single or double.
	unicode_header_linestyle
	Sets the header drawing style for the unicode line style to one of single or double.
	Illustrations of how these different formats look can be seen in the Examples section.
	Tip: There are various shortcut commands for \pset. See \a, \C, \H, \t, \T, and \x.
\q or \quit	Quits the psql program. In a script file, only execution of that script is terminated.
\qecho text [ ... ]	This command is identical to \echo except that the output will be written to the query output channel, as set by \o.
\r or \reset	Resets (clears) the query buffer.
\s [ filename ]	Print psql's command line history to filename. If filename is omitted, the history is written to the standard output (using the pager if appropriate). This command is not available if psql was built without Readline support.
\set [ name [ value [ ... ] ] ]	Sets the psql variable name to value, or if more than one value is given, to the concatenation of all of them. If only one argument is given, the variable is set with an empty value. To unset a variable, use the \unset command.
	\set without any arguments displays the names and values of all currently-set psql variables.
	Valid variable names can contain letters, digits, and underscores. See the section Variables below for details. Variable names are case-sensitive.
	Although you are welcome to set any variable to anything you want, psql treats several variables as special. They are documented in the section about variables.
	Note: This command is unrelated to the SQL command SET.
\setenv name [ value ]	Sets the environment variable name to value, or if the value is not supplied, unsets the environment variable. Example:
	testdb=> \setenv PAGER less
	testdb=> \setenv LESS -imx4F
\sf[+] function_description	This command fetches and shows the definition of the named function, in the form of a CREATE OR REPLACE FUNCTION command. The definition is printed to the current query output channel, as set by \o.
	The target function can be specified by name alone, or by name and arguments, for example foo(integer, text). The argument types must be given if there is more than one function of the same name.
	If + is appended to the command name, then the output lines are numbered, with the first line of the function body being line 1.
\t	Toggles the display of output column name headings and row count footer. This command is equivalent to \pset tuples_only and is provided for convenience.
\T table_options	Specifies attributes to be placed within the table tag in HTML output format. This command is equivalent to \pset tableattr table_options.
\timing [ on | off ]	Without parameter, toggles a display of how long each SQL statement takes, in milliseconds. With parameter, sets same.
\unset name	Unsets (deletes) the psql variable name.
\w or \write filename
\w or \write |command	Outputs the current query buffer to the file filename or pipes it to the shell command command.
\watch [ seconds ]	Repeatedly execute the current query buffer (like \g) until interrupted or the query fails. Wait the specified number of seconds (default 2) between executions.
\x [ on | off | auto ]	Sets or toggles expanded table formatting mode. As such it is equivalent to \pset expanded.
\z [ pattern ]	Lists tables, views and sequences with their associated access privileges. If a pattern is specified, only tables, views and sequences whose names match the pattern are listed.
	This is an alias for \dp ("display privileges").
\! [ command ]	Escapes to a separate shell or executes the shell command command. The arguments are not further interpreted; the shell will see them as-is. In particular, the variable substitution rules and backslash escapes do not apply.
\? [ topic ]	Shows help information. The optional topic parameter (defaulting to commands) selects which part of psql is explained: commands describes psql's backslash commands; options describes the command-line options that can be passed to psql; and variables shows help about psql configuration variables.

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

pg_ctl
Name
pg_ctl -- initialize, start, stop, or control a PostgreSQL server
Synopsis

pg_ctl init[db] [-s] [-D datadir] [-o initdb-options]

pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path] [-c]

pg_ctl stop [-W] [-t seconds] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ]

pg_ctl restart [-w] [-t seconds] [-s] [-D datadir] [-c] [-m s[mart] | f[ast] | i[mmediate] ] [-o options]

pg_ctl reload [-s] [-D datadir]

pg_ctl status [-D datadir]

pg_ctl promote [-s] [-D datadir]

pg_ctl kill signal_name process_id

pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-S a[uto] | d[emand] ] [-w] [-t seconds] [-s] [-o options]

pg_ctl unregister [-N servicename]