PostgreSQL Cookbook: Difference between revisions
Jump to navigation
Jump to search
| (9 intermediate revisions by the same user not shown) | |||
| Line 14: | Line 14: | ||
<pre> | <pre> | ||
$ pip install psycopg2 | $ pip install psycopg2 | ||
</pre> | |||
==== PostgreSQL GUIs ==== | |||
* [https://www.pgadmin.org/ pgAdmin] | |||
* [https://eggerapps.at/postico/ Postico] | |||
==== Troubleshooting ==== | |||
'''Compile errors installing `psycopg2` involving missing `-lssl` libraries:''' <ref>[https://stackoverflow.com/questions/26288042/error-installing-psycopg2-library-not-found-for-lssl Error installing psycopg2 library not found for -lssl] - StackOverflow</ref> | |||
Make sure openssl is installed with `brew install openssl`. | |||
Then link against brew's openssl (from within a virtual environment): | |||
<pre> | |||
(venv) $ env LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib" pip --no-cache install psycopg2 | |||
</pre> | </pre> | ||
| Line 19: | Line 36: | ||
Search for the "PostgreSQL Windows". There is an installer available. It's pretty straightforward. | Search for the "PostgreSQL Windows". There is an installer available. It's pretty straightforward. | ||
== Server properties == | |||
=== Confirm that the server is running === | |||
<pre> | |||
$ pg_ctl status | |||
</pre> | |||
== Creating a database == | == Creating a database == | ||
| Line 27: | Line 52: | ||
* Create the database<br />'''Object browser''' > '''Server Groups''' > ''[server]'' > '''Databases''' > ''(right click)'' > '''New Database''' | * Create the database<br />'''Object browser''' > '''Server Groups''' > ''[server]'' > '''Databases''' > ''(right click)'' > '''New Database''' | ||
== | == Starting and stopping the server == | ||
* Run '''pgAdmin | === Starting the server === | ||
<pre> | |||
$ pg_ctl -l $PGDATA/server.log start | |||
</pre> | |||
This assumes that `PGDATA` has been defined in the shell profile, e.g. | |||
<pre> | |||
export PGDATA=`/usr/local/var/postgres` | |||
</pre> | |||
=== Stopping the server === | |||
<pre> | |||
$ pg_ctl stop -m fast | |||
</pre> | |||
=== Restarting the server === | |||
* Run '''pgAdmin 4'''. | |||
* Connect to the local server. | * Connect to the local server. | ||
* '''Tools''' > '''Server Configuration''' > '''pg_hba.conf''' or '''postgresql.conf''' > '''File''' > '''Reload Server''' | * '''Tools''' > '''Server Configuration''' > '''pg_hba.conf''' or '''postgresql.conf''' > '''File''' > '''Reload Server''' | ||
=== Restarting manually === | ==== Restarting manually ==== | ||
<pre> | |||
$ pg_ctl reload | |||
</pre> | |||
<div class="alert alert-warning">I am including the information below because it's the most commonly documented method. It did not work reliably on Windows, however. It seems like not every process would be killed, and that some of them would still be attached to port 5432. The service could not be restarted after the server was stopped.</div> | <div class="alert alert-warning">I am including the information below because it's the most commonly documented method. It did not work reliably on Windows, however. It seems like not every process would be killed, and that some of them would still be attached to port 5432. The service could not be restarted after the server was stopped.</div> | ||
| Line 53: | Line 102: | ||
* Check the status of the postgresql service. (Its name will end with the version number.) | * Check the status of the postgresql service. (Its name will end with the version number.) | ||
== Allowing remote access for pgAdmin | == Allowing remote access for pgAdmin 4 == | ||
* Make sure that the PostgreSQL port (default 5432) is open on the host machine. | * Make sure that the PostgreSQL port (default 5432) is open on the host machine. | ||
| Line 65: | Line 114: | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
$ psql -U [USER_NAME] [ | $ psql -U [USER_NAME] [DATABASE_NAME] | ||
</syntaxhighlight> | |||
== Importing data == | |||
From the command line use `psql`: | |||
<syntaxhighlight lang="bash"> | |||
$ psql -U [USER_NAME] [DATABASE_NAME] < [EXPORTED_DATA_FILE_NAME].sql | |||
</syntaxhighlight> | </syntaxhighlight> | ||
== Notes == | == Notes == | ||
<references /> | <references /> | ||
Latest revision as of 17:27, 28 November 2020
Installation[edit]
Mac OS[edit]
Use homebrew from the command line: [1]
$ brew install postgresql
Install the pyscopg library for PostgreSQL support within Python: [2]
$ pip install psycopg2
PostgreSQL GUIs[edit]
Troubleshooting[edit]
Compile errors installing psycopg2 involving missing -lssl libraries: [3]
Make sure openssl is installed with brew install openssl.
Then link against brew's openssl (from within a virtual environment):
(venv) $ env LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib" pip --no-cache install psycopg2
Windows[edit]
Search for the "PostgreSQL Windows". There is an installer available. It's pretty straightforward.
Server properties[edit]
Confirm that the server is running[edit]
$ pg_ctl status
Creating a database[edit]
Use the pgAdmin III program.
- First create a Login Role for the database. Set the database's owner to the new (or existing) login role:
Object browser > Server Groups > [server] > Login Roles > (right click) > New Login Role - Create the database
Object browser > Server Groups > [server] > Databases > (right click) > New Database
Starting and stopping the server[edit]
Starting the server[edit]
$ pg_ctl -l $PGDATA/server.log start
This assumes that PGDATA has been defined in the shell profile, e.g.
export PGDATA=`/usr/local/var/postgres`
Stopping the server[edit]
$ pg_ctl stop -m fast
Restarting the server[edit]
- Run pgAdmin 4.
- Connect to the local server.
- Tools > Server Configuration > pg_hba.conf or postgresql.conf > File > Reload Server
Restarting manually[edit]
$ pg_ctl reload
I am including the information below because it's the most commonly documented method. It did not work reliably on Windows, however. It seems like not every process would be killed, and that some of them would still be attached to port 5432. The service could not be restarted after the server was stopped.
The PostgreSQL service name includes the version number.
With powershell run with Administrator privileges, navigate to the bin\ directory in the PostgreSQL installation directory.
> .\wp_ctl.exe restart -w -D "c:\ProgramData\PostgreSQL\9.4\data\"
-wWait for the server to stop and start.-DPath to data directory. The version number may be different depending on the installation.[4]
This appears to stop the PostgreSQL service & requires the service to be manually restarted.
- Start Menu > Services (command line:
services.msc) - Check the status of the postgresql service. (Its name will end with the version number.)
Allowing remote access for pgAdmin 4[edit]
- Make sure that the PostgreSQL port (default 5432) is open on the host machine.
- Edit
data\pg_hba.confin the PosgreSQL installation directory to give the remote machine access. (Requires the PostgreSQL server to be restarted.)[5] [6] - Confirm that that the PostgreSQL service is running in the Services control panel.
- ipv6 address must be added to
pg_hba.confon Windows. Append/128to open the entire subnet of local addresses.
Querying a database from the command line[edit]
To start an interactive session:
$ psql -U [USER_NAME] [DATABASE_NAME]
Importing data[edit]
From the command line use psql:
$ psql -U [USER_NAME] [DATABASE_NAME] < [EXPORTED_DATA_FILE_NAME].sql
Notes[edit]
- ↑ Getting started with PostgreSQL on Mac OS - Codementor Community
- ↑ Psycopg Installation - Psycopg Documentation
- ↑ Error installing psycopg2 library not found for -lssl - StackOverflow
- ↑ pg_ctl (PostgreSQL documentation)
- ↑ The pg_hba.conf File PostgreSQL documentation
- ↑ IP range in pg_hba.conf? (PostgreSQL forums)
There is an explanation of CIDR masks at the end of the thread.