Getting started with postgresql Introduction Topic

Examples

• 2

  Install PostgreSQL from Source on Linux
  2

  Dependencies:

  • GNU Make Version > 3.80
  • an ISO/ ANSI C-Compiler (e.g. gcc)
  • an extractor like tar or gzip
  • zlib-devel
  • readline-devel oder libedit-devel

  Sources: Link to the latest source (9.6.2)

  Now you can extract the source files:

  tar -xzvf postgresql-9.6.2.tar.gz
  

  There are a large number of different options for the configuration of PostgreSQL:

  Full Link to the full installation procedure

  Small list of available options:

  • --prefix=PATH path for all files
  • --exec-prefix=PATH path for architectur-dependet file
  • --bindir=PATH path for executable programs
  • --sysconfdir=PATH path for configuration files
  • --with-pgport=NUMBER specify a port for your server
  • --with-perl add perl support
  • --with-python add python support
  • --with-openssl add openssl support
  • --with-ldap add ldap support
  • --with-blocksize=BLOCKSIZE set pagesize in KB
    • BLOCKSIZE must a power of 2 and between 1 and 32
  • --with-wal-segsize=SEGSIZE set size of WAL-Segment size in MB
    • SEGSIZE must be a power of 2 between 1 and 64

  Go into the new created folder and run the cofigure script with the desired options:

  ./configure --exec=/usr/local/pgsql
  

  Run make to create the objectfiles

  Run make install to install PostgreSQL from the built files

  Run make clean to tidy up

  For the extension switch the directory cd contrib, run make and make install

  edited Feb 14 at 8:52

  
  edit share view discussion request improvement

  edit move example delete example
• 2

  Installing PostgreSQL on Windows
  2

  While it's good practice to use a Unix based operating system (ex. Linux or BSD) as a production server you can easily install PostgreSQL on Windows (hopefully only as a development server).

  Download the Windows installation binaries from EnterpriseDB: http://www.enterprisedb.com/products-services-training/pgdownload This is a third-party company started by core contributors to the PostgreSQL project who have optimized the binaries for Windows.

  Select the latest stable (non-Beta) version (9.5.3 at the time of writing). You will most likely want the Win x86-64 package, but if you are running a 32 bit version of Windows, which is common on older computers, select Win x86-32 instead.

  You can check if your version of Windows is 32 or 64 bit by going to Control Panel -> System and Security -> System -> System type, which will say "##-bit Operating System". This is the path for Windows 7, it may be slightly different on other versions of Windows.

  In the installer select the packages you would like to use. For example:

  • pgAdmin ( https://www.pgadmin.org ) is a free GUI for managing your database and I highly recommend it.
  • PostGIS ( http://postgis.net ) provides unparalleled geospatial analysis features, many GIS developers switch to PostgreSQL primarily because of PostGIS. If you don't know what GIS is you probably don't need it, but remember its name if you ever need to develop an application which uses GPS coordinates, distances, etc.
  • Procedural languages like PL/Python or PL/Perl can be useful once you start writing functions (aka stored procedures) but can also be installed later. There are many other languages available than those included in the installer, such as PLV8 ( http://www.postgresonline.com/journal/archives/360-PLV8-binaries-for-PostgreSQL-9.5-windows-both-32-bit-and-64-bit.html ) which uses compiled JavaScript, much like Node.js.
  • Other packages like pgAgent, pgBouncer and Slony are useful for larger production servers but can be left unchecked if you are still learning the basics, you can always install them later.

  Open pgAdmin and connect to your server by double clicking on its name, ex. "PostgreSQL 9.5 (localhost:5432).

  From this point you can follow guides such as the excellent book PostgreSQL: Up and Running, 2nd Edition ( http://shop.oreilly.com/product/0636920032144.do ).

  Optional: Manual Service Startup Type

  PostgreSQL runs as a service in the background which is slightly different than most programs. This is common for databases and web servers. Its default Startup Type is Automatic which means it will always run without any input from you.

  Why would you want to manually control the PostgreSQL service? If you're using your PC as a development server some of the time and but also use it to play video games for example, PostegreSQL could slow down your system a bit while its running.

  Why wouldn't you want manual control? Starting and stopping the service can be a hassle if you do it often.

  If you don't notice any difference in speed and prefer avoiding the hassle then leave its Startup Type as Automatic and ignore the rest of this guide. Otherwise...

  Go to Control Panel -> System and Security -> Administrative Tools.

  Select "Services" from the list, right click on its icon, and select Send To -> Desktop to create a desktop icon for more convenient access.

  Close the Administrative Tools window then launch Services from the desktop icon you just created.

  Scroll down until you see a service with a name like postgresql-x##-9.# (ex. "postgresql-x64-9.5").

  Right click on the postgres service, select Properties -> Startup type -> Manual -> Apply -> OK. You can change it back to automatic just as easily.

  If you see other PostgreSQL related services in the list such "pgbouncer" or "PostgreSQL Scheduling Agent - pgAgent" you can also change their Startup Type to Manual because they're not much use if PostgreSQL isn't running. Although this will mean more hassle each time you start and stop so it's up to you. They don't use as many resources as PostgreSQL itself and may not have any noticeable impact on your systems performance.

  If the service is running its Status will say Started, otherwise it isn't running.

  To start it right click and select Start. A loading prompt will be displayed and should disappear on its own soon after. If it gives you an error try a second time. If that doesn't work then there was some problem with the installation, possibly because you changed some setting in Windows most people don't change, so finding the problem might require some sleuthing.

  To stop postgres right click on the service and select Stop.

  If you ever get an error while attempting to connect to your database check Services to make sure its running.

  created Aug 8 '16 at 10:01

  
  edit share view discussion request improvement

  edit move example delete example
• 0

  How to install PostgreSQL via MacPorts on OSX
  0

  In order to install PostgreSQL on OSX, you need to know which versions are currently supported.

  Use this command to see what versions you have available.

  sudo port list | grep "^postgresql[[:digit:]]\{2\}[[:space:]]"
  

  You should get a list that looks something like the following:

  postgresql80                   @8.0.26         databases/postgresql80
  postgresql81                   @8.1.23         databases/postgresql81
  postgresql82                   @8.2.23         databases/postgresql82
  postgresql83                   @8.3.23         databases/postgresql83
  postgresql84                   @8.4.22         databases/postgresql84
  postgresql90                   @9.0.23         databases/postgresql90
  postgresql91                   @9.1.22         databases/postgresql91
  postgresql92                   @9.2.17         databases/postgresql92
  postgresql93                   @9.3.13         databases/postgresql93
  postgresql94                   @9.4.8          databases/postgresql94
  postgresql95                   @9.5.3          databases/postgresql95
  postgresql96                   @9.6beta2       databases/postgresql96
  

  In this example, the most recent version of PostgreSQL that is supported in 9.6, so we will install that.

  sudo port install postgresql96-server postgresql96
  

  You will see an installation log like this:

  --->  Computing dependencies for postgresql96-server
  --->  Dependencies to be installed: postgresql96
  --->  Fetching archive for postgresql96
  --->  Attempting to fetch postgresql96-9.6beta2_0.darwin_15.x86_64.tbz2 from         https://packages.macports.org/postgresql96
  --->  Attempting to fetch postgresql96-9.6beta2_0.darwin_15.x86_64.tbz2.rmd160 from https://packages.macports.org/postgresql96
  --->  Installing postgresql96 @9.6beta2_0
  --->  Activating postgresql96 @9.6beta2_0
  
  To use the postgresql server, install the postgresql96-server port
  
  --->  Cleaning postgresql96
  --->  Fetching archive for postgresql96-server
  --->  Attempting to fetch postgresql96-server-9.6beta2_0.darwin_15.x86_64.tbz2 from https://packages.macports.org/postgresql96-server
  --->  Attempting to fetch postgresql96-server-9.6beta2_0.darwin_15.x86_64.tbz2.rmd160 from https://packages.macports.org/postgresql96-server
  --->  Installing postgresql96-server @9.6beta2_0
  --->  Activating postgresql96-server @9.6beta2_0
  
  To create a database instance, after install do
   sudo mkdir -p /opt/local/var/db/postgresql96/defaultdb
   sudo chown postgres:postgres /opt/local/var/db/postgresql96/defaultdb
   sudo su postgres -c '/opt/local/lib/postgresql96/bin/initdb -D /opt/local/var/db/postgresql96/defaultdb'
  
  --->  Cleaning postgresql96-server
  --->  Computing dependencies for postgresql96
  --->  Cleaning postgresql96
  --->  Updating database of binaries
  --->  Scanning binaries for linking errors
  --->  No broken files found.
  

  The log provides instructions on the rest of the steps for installation, so we do that next.

  sudo mkdir -p /opt/local/var/db/postgresql96/defaultdb
  sudo chown postgres:postgres /opt/local/var/db/postgresql96/defaultdb
  sudo su postgres -c '/opt/local/lib/postgresql96/bin/initdb -D /opt/local/var/db/postgresql96/defaultdb'
  

  Now we start the server:

  sudo port load -w postgresql96-server
  

  Verify that we can connect to the server:

  su postgres -c psql
  

  You will see a prompt from postgres:

  psql (9.6.1)
  Type "help" for help.
  
  postgres=#
  

  Here you can type a query to see that the server is running.

  postgres=#SELECT setting FROM pg_settings WHERE name='data_directory';
  

  And see the response:

                  setting
  ------------------------------------------
  /opt/local/var/db/postgresql96/defaultdb
  (1 row)
  postgres=#
  

  Type \q to quit:

  postgres=#\q
  

  And you will be back at your shell prompt.

  Congratulations! You now have a running PostgreSQL instance on OS/X.

  created Jul 22 '16 at 8:54

  
  edit share view discussion request improvement

  edit move example delete example

• Improvements requested:

  • This example is completely unclear, incomplete, or has severe formatting problems. It is unlikely to be salvageable through editing and should be removed. –  Kirk Roybal Feb 6 at 20:08

    This only shows how to install the client utilities, not the server. It obviously follows that it doesn't include the server setup, either. It looks like it is intended to show how simple brew is, but the example is abbreviated to the point of libel.

  Edit Example Delete Example
  0

  Install postgresql with brew on Mac
  0

  brew update
  brew install postgresql
  

  created Sep 19 '16 at 1:05

  
  edit share view discussion request improvement (1)

  edit move example delete example

• Improvements requested:

  • Other: It is specific to certain Linux distributions and does not cover Windows at all. Also, a reference to 9.6 is out of place; that will become obsoletee before you can blink. It might be better to say that the PGDG offers the most current vresions. –  Laurenz Albe Jul 22 at 7:15

  Edit Example Delete Example
  0

  Installation on GNU+Linux
  0

  On most GNU+Linux operating systems, PostgreSQL can easily be installed using the operating system package manager.

  Red Hat family

  On Red Hat and derived operating systems, type:

  sudo yum install postgresql-server
  

  Debian family

  On Debian and derived operating systems, type:

  sudo apt-get install postgresql
  

  This will install the PostgreSQL server package, at the default version offered by the operating system's package repositories.

  If the version that's installed by default is not the one that you want, you can use the package manager to search for specific versions which may simultaneously be offered.

  You can also use the Yum repository provided by the PostgreSQL project (known as PGDG) to get a different version. This may allow versions not yet offered by operating system package repositories.

  edited Sep 11 '16 at 19:00

  

  

  
  edit share view discussion request improvement (1)

  edit move example delete example
• 0

  Postgres.app for Mac OSX
  0

  An extremely simple tool for installing PostgreSQL on a Mac is available by downloading Postgres.app.
  You can change preferences to have PostgreSQL run in the background or only when the application is running.
  

  edited Aug 3 '16 at 14:00

  

  
  edit share view discussion request improvement

  edit move example delete example