Archive for the ‘PostgreSQL Installation’ tag
Add PostGIS to PostgreSQL
The following blog post shows you how to add PostGIS and PgRouting to your existing install of PostgeSQL 14 on the Ubuntu Desktop, Version 22.0.4. This blog post relies on information in this earlier Install and Configure PostgreSQL on Ubuntu post. Generalized documentation on PostGIS exists at this URL.
You install the postgis libraries:
sudo apt install -y postgis |
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: gdal-data libaec0 libaom3 libarmadillo10 libarpack2 libblosc1 libboost-serialization1.74.0 libcfitsio9 libcharls2 libdav1d5 libde265-0 libfreexl1 libfyba0 libgdal30 libgeos-c1v5 libgeos3.10.2 libgeotiff5 libgmpxx4ldbl libhdf4-0-alt libhdf5-103-1 libhdf5-hl-100 libheif1 libkmlbase1 libkmldom1 libkmlengine1 libminizip1 libnetcdf19 libodbc2 libodbcinst2 libogdi4.1 libproj22 libprotobuf-c1 libqhull-r8.0 librttopo1 libsfcgal1 libsnappy1v5 libspatialite7 libsuperlu5 libsz2 liburiparser1 libx265-199 libxerces-c3.2 postgis-doc postgresql-14-postgis-3 postgresql-14-postgis-3-scripts proj-bin proj-data unixodbc-common Suggested packages: geotiff-bin gdal-bin libgeotiff-epsg libhdf4-doc libhdf4-alt-dev hdf4-tools odbc-postgresql tdsodbc ogdi-bin The following NEW packages will be installed: gdal-data libaec0 libaom3 libarmadillo10 libarpack2 libblosc1 libboost-serialization1.74.0 libcfitsio9 libcharls2 libdav1d5 libde265-0 libfreexl1 libfyba0 libgdal30 libgeos-c1v5 libgeos3.10.2 libgeotiff5 libgmpxx4ldbl libhdf4-0-alt libhdf5-103-1 libhdf5-hl-100 libheif1 libkmlbase1 libkmldom1 libkmlengine1 libminizip1 libnetcdf19 libodbc2 libodbcinst2 libogdi4.1 libproj22 libprotobuf-c1 libqhull-r8.0 librttopo1 libsfcgal1 libsnappy1v5 libspatialite7 libsuperlu5 libsz2 liburiparser1 libx265-199 libxerces-c3.2 postgis postgis-doc postgresql-14-postgis-3 postgresql-14-postgis-3-scripts proj-bin proj-data unixodbc-common 0 upgraded, 49 newly installed, 0 to remove and 6 not upgraded. Need to get 42.0 MB of archives. After this operation, 173 MB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 gdal-data all 3.4.1+dfsg-1build4 [216 kB] Get:2 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libaec0 amd64 1.0.6-1 [20.1 kB] Get:3 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libaom3 amd64 3.3.0-1 [1,748 kB] Get:4 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libarpack2 amd64 3.8.0-1 [92.4 kB] Get:5 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libsuperlu5 amd64 5.3.0+dfsg1-2 [183 kB] Get:6 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libarmadillo10 amd64 1:10.8.2+dfsg-1 [105 kB] Get:7 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libsnappy1v5 amd64 1.1.8-1build3 [17.5 kB] Get:8 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libblosc1 amd64 1.21.1+ds2-2 [35.8 kB] Get:9 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libboost-serialization1.74.0 amd64 1.74.0-14ubuntu3 [327 kB] Get:10 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libcfitsio9 amd64 4.0.0-1 [519 kB] Get:11 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libcharls2 amd64 2.3.4-1 [87.0 kB] Get:12 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libdav1d5 amd64 0.9.2-1 [463 kB] Get:13 http://us.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 libde265-0 amd64 1.0.8-1ubuntu0.1 [289 kB] Get:14 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libfyba0 amd64 4.1.1-7 [113 kB] Get:15 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libfreexl1 amd64 1.0.6-1 [33.5 kB] Get:16 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libgeos3.10.2 amd64 3.10.2-1 [713 kB] Get:17 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libgeos-c1v5 amd64 3.10.2-1 [82.5 kB] Get:18 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 proj-data all 8.2.1-1 [10.0 MB] Get:19 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libproj22 amd64 8.2.1-1 [1,257 kB] Get:20 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libgeotiff5 amd64 1.7.0-2build1 [67.1 kB] Get:21 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libhdf4-0-alt amd64 4.2.15-4 [290 kB] Get:22 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libsz2 amd64 1.0.6-1 [5,354 B] Get:23 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libhdf5-103-1 amd64 1.10.7+repack-4ubuntu2 [1,295 kB] Get:24 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libx265-199 amd64 3.5-2 [1,170 kB] Get:25 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libheif1 amd64 1.12.0-2build1 [196 kB] Get:26 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libminizip1 amd64 1.1-8build1 [20.2 kB] Get:27 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 liburiparser1 amd64 0.9.6+dfsg-1 [36.4 kB] Get:28 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libkmlbase1 amd64 1.3.0-9 [45.0 kB] Get:29 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libkmldom1 amd64 1.3.0-9 [150 kB] Get:30 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libkmlengine1 amd64 1.3.0-9 [71.7 kB] Get:31 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libhdf5-hl-100 amd64 1.10.7+repack-4ubuntu2 [59.1 kB] Get:32 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libnetcdf19 amd64 1:4.8.1-1 [456 kB] Get:33 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libodbc2 amd64 2.3.9-5 [159 kB] Get:34 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 unixodbc-common all 2.3.9-5 [9,228 B] Get:35 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libodbcinst2 amd64 2.3.9-5 [31.9 kB] Get:36 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libogdi4.1 amd64 4.1.0+ds-5 [197 kB] Get:37 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libqhull-r8.0 amd64 2020.2-4 [196 kB] Get:38 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 librttopo1 amd64 1.1.0-2 [178 kB] Get:39 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libspatialite7 amd64 5.0.1-2build2 [2,092 kB] Get:40 http://us.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 libxerces-c3.2 amd64 3.2.3+debian-3ubuntu0.1 [929 kB] Get:41 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libgdal30 amd64 3.4.1+dfsg-1build4 [7,642 kB] Get:42 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libgmpxx4ldbl amd64 2:6.2.1+dfsg-3ubuntu1 [9,580 B] Get:43 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libprotobuf-c1 amd64 1.3.3-1ubuntu2.1 [20.3 kB] Get:44 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libsfcgal1 amd64 1.4.1-1 [2,179 kB] Get:45 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgis amd64 3.2.0+dfsg-1ubuntu1 [350 kB] Get:46 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgis-doc all 3.2.0+dfsg-1ubuntu1 [2,922 kB] Get:47 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgresql-14-postgis-3-scripts all 3.2.0+dfsg-1ubuntu1 [1,018 kB] Get:48 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgresql-14-postgis-3 amd64 3.2.0+dfsg-1ubuntu1 [3,696 kB] Get:49 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 proj-bin amd64 8.2.1-1 [154 kB] Fetched 42.0 MB in 7s (5,994 kB/s) Extracting templates from packages: 100% Selecting previously unselected package gdal-data. (Reading database ... 249055 files and directories currently installed.) Preparing to unpack .../00-gdal-data_3.4.1+dfsg-1build4_all.deb ... Unpacking gdal-data (3.4.1+dfsg-1build4) ... Selecting previously unselected package libaec0:amd64. Preparing to unpack .../01-libaec0_1.0.6-1_amd64.deb ... Unpacking libaec0:amd64 (1.0.6-1) ... Selecting previously unselected package libaom3:amd64. Preparing to unpack .../02-libaom3_3.3.0-1_amd64.deb ... Unpacking libaom3:amd64 (3.3.0-1) ... Selecting previously unselected package libarpack2:amd64. Preparing to unpack .../03-libarpack2_3.8.0-1_amd64.deb ... Unpacking libarpack2:amd64 (3.8.0-1) ... Selecting previously unselected package libsuperlu5:amd64. Preparing to unpack .../04-libsuperlu5_5.3.0+dfsg1-2_amd64.deb ... Unpacking libsuperlu5:amd64 (5.3.0+dfsg1-2) ... Selecting previously unselected package libarmadillo10. Preparing to unpack .../05-libarmadillo10_1%3a10.8.2+dfsg-1_amd64.deb ... Unpacking libarmadillo10 (1:10.8.2+dfsg-1) ... Selecting previously unselected package libsnappy1v5:amd64. Preparing to unpack .../06-libsnappy1v5_1.1.8-1build3_amd64.deb ... Unpacking libsnappy1v5:amd64 (1.1.8-1build3) ... Selecting previously unselected package libblosc1:amd64. Preparing to unpack .../07-libblosc1_1.21.1+ds2-2_amd64.deb ... Unpacking libblosc1:amd64 (1.21.1+ds2-2) ... Selecting previously unselected package libboost-serialization1.74.0:amd64. Preparing to unpack .../08-libboost-serialization1.74.0_1.74.0-14ubuntu3_amd64.deb ... Unpacking libboost-serialization1.74.0:amd64 (1.74.0-14ubuntu3) ... Selecting previously unselected package libcfitsio9:amd64. Preparing to unpack .../09-libcfitsio9_4.0.0-1_amd64.deb ... Unpacking libcfitsio9:amd64 (4.0.0-1) ... Selecting previously unselected package libcharls2:amd64. Preparing to unpack .../10-libcharls2_2.3.4-1_amd64.deb ... Unpacking libcharls2:amd64 (2.3.4-1) ... Selecting previously unselected package libdav1d5:amd64. Preparing to unpack .../11-libdav1d5_0.9.2-1_amd64.deb ... Unpacking libdav1d5:amd64 (0.9.2-1) ... Selecting previously unselected package libde265-0:amd64. Preparing to unpack .../12-libde265-0_1.0.8-1ubuntu0.1_amd64.deb ... Unpacking libde265-0:amd64 (1.0.8-1ubuntu0.1) ... Selecting previously unselected package libfyba0:amd64. Preparing to unpack .../13-libfyba0_4.1.1-7_amd64.deb ... Unpacking libfyba0:amd64 (4.1.1-7) ... Selecting previously unselected package libfreexl1:amd64. Preparing to unpack .../14-libfreexl1_1.0.6-1_amd64.deb ... Unpacking libfreexl1:amd64 (1.0.6-1) ... Selecting previously unselected package libgeos3.10.2:amd64. Preparing to unpack .../15-libgeos3.10.2_3.10.2-1_amd64.deb ... Unpacking libgeos3.10.2:amd64 (3.10.2-1) ... Selecting previously unselected package libgeos-c1v5:amd64. Preparing to unpack .../16-libgeos-c1v5_3.10.2-1_amd64.deb ... Unpacking libgeos-c1v5:amd64 (3.10.2-1) ... Selecting previously unselected package proj-data. Preparing to unpack .../17-proj-data_8.2.1-1_all.deb ... Unpacking proj-data (8.2.1-1) ... Selecting previously unselected package libproj22:amd64. Preparing to unpack .../18-libproj22_8.2.1-1_amd64.deb ... Unpacking libproj22:amd64 (8.2.1-1) ... Selecting previously unselected package libgeotiff5:amd64. Preparing to unpack .../19-libgeotiff5_1.7.0-2build1_amd64.deb ... Unpacking libgeotiff5:amd64 (1.7.0-2build1) ... Selecting previously unselected package libhdf4-0-alt. Preparing to unpack .../20-libhdf4-0-alt_4.2.15-4_amd64.deb ... Unpacking libhdf4-0-alt (4.2.15-4) ... Selecting previously unselected package libsz2:amd64. Preparing to unpack .../21-libsz2_1.0.6-1_amd64.deb ... Unpacking libsz2:amd64 (1.0.6-1) ... Selecting previously unselected package libhdf5-103-1:amd64. Preparing to unpack .../22-libhdf5-103-1_1.10.7+repack-4ubuntu2_amd64.deb ... Unpacking libhdf5-103-1:amd64 (1.10.7+repack-4ubuntu2) ... Selecting previously unselected package libx265-199:amd64. Preparing to unpack .../23-libx265-199_3.5-2_amd64.deb ... Unpacking libx265-199:amd64 (3.5-2) ... Selecting previously unselected package libheif1:amd64. Preparing to unpack .../24-libheif1_1.12.0-2build1_amd64.deb ... Unpacking libheif1:amd64 (1.12.0-2build1) ... Selecting previously unselected package libminizip1:amd64. Preparing to unpack .../25-libminizip1_1.1-8build1_amd64.deb ... Unpacking libminizip1:amd64 (1.1-8build1) ... Selecting previously unselected package liburiparser1:amd64. Preparing to unpack .../26-liburiparser1_0.9.6+dfsg-1_amd64.deb ... Unpacking liburiparser1:amd64 (0.9.6+dfsg-1) ... Selecting previously unselected package libkmlbase1:amd64. Preparing to unpack .../27-libkmlbase1_1.3.0-9_amd64.deb ... Unpacking libkmlbase1:amd64 (1.3.0-9) ... Selecting previously unselected package libkmldom1:amd64. Preparing to unpack .../28-libkmldom1_1.3.0-9_amd64.deb ... Unpacking libkmldom1:amd64 (1.3.0-9) ... Selecting previously unselected package libkmlengine1:amd64. Preparing to unpack .../29-libkmlengine1_1.3.0-9_amd64.deb ... Unpacking libkmlengine1:amd64 (1.3.0-9) ... Selecting previously unselected package libhdf5-hl-100:amd64. Preparing to unpack .../30-libhdf5-hl-100_1.10.7+repack-4ubuntu2_amd64.deb ... Unpacking libhdf5-hl-100:amd64 (1.10.7+repack-4ubuntu2) ... Selecting previously unselected package libnetcdf19:amd64. Preparing to unpack .../31-libnetcdf19_1%3a4.8.1-1_amd64.deb ... Unpacking libnetcdf19:amd64 (1:4.8.1-1) ... Selecting previously unselected package libodbc2:amd64. Preparing to unpack .../32-libodbc2_2.3.9-5_amd64.deb ... Unpacking libodbc2:amd64 (2.3.9-5) ... Selecting previously unselected package unixodbc-common. Preparing to unpack .../33-unixodbc-common_2.3.9-5_all.deb ... Unpacking unixodbc-common (2.3.9-5) ... Selecting previously unselected package libodbcinst2:amd64. Preparing to unpack .../34-libodbcinst2_2.3.9-5_amd64.deb ... Unpacking libodbcinst2:amd64 (2.3.9-5) ... Selecting previously unselected package libogdi4.1. Preparing to unpack .../35-libogdi4.1_4.1.0+ds-5_amd64.deb ... Unpacking libogdi4.1 (4.1.0+ds-5) ... Selecting previously unselected package libqhull-r8.0:amd64. Preparing to unpack .../36-libqhull-r8.0_2020.2-4_amd64.deb ... Unpacking libqhull-r8.0:amd64 (2020.2-4) ... Selecting previously unselected package librttopo1:amd64. Preparing to unpack .../37-librttopo1_1.1.0-2_amd64.deb ... Unpacking librttopo1:amd64 (1.1.0-2) ... Selecting previously unselected package libspatialite7:amd64. Preparing to unpack .../38-libspatialite7_5.0.1-2build2_amd64.deb ... Unpacking libspatialite7:amd64 (5.0.1-2build2) ... Selecting previously unselected package libxerces-c3.2:amd64. Preparing to unpack .../39-libxerces-c3.2_3.2.3+debian-3ubuntu0.1_amd64.deb ... Unpacking libxerces-c3.2:amd64 (3.2.3+debian-3ubuntu0.1) ... Selecting previously unselected package libgdal30. Preparing to unpack .../40-libgdal30_3.4.1+dfsg-1build4_amd64.deb ... Unpacking libgdal30 (3.4.1+dfsg-1build4) ... Selecting previously unselected package libgmpxx4ldbl:amd64. Preparing to unpack .../41-libgmpxx4ldbl_2%3a6.2.1+dfsg-3ubuntu1_amd64.deb ... Unpacking libgmpxx4ldbl:amd64 (2:6.2.1+dfsg-3ubuntu1) ... Selecting previously unselected package libprotobuf-c1:amd64. Preparing to unpack .../42-libprotobuf-c1_1.3.3-1ubuntu2.1_amd64.deb ... Unpacking libprotobuf-c1:amd64 (1.3.3-1ubuntu2.1) ... Selecting previously unselected package libsfcgal1. Preparing to unpack .../43-libsfcgal1_1.4.1-1_amd64.deb ... Unpacking libsfcgal1 (1.4.1-1) ... Selecting previously unselected package postgis. Preparing to unpack .../44-postgis_3.2.0+dfsg-1ubuntu1_amd64.deb ... Unpacking postgis (3.2.0+dfsg-1ubuntu1) ... Selecting previously unselected package postgis-doc. Preparing to unpack .../45-postgis-doc_3.2.0+dfsg-1ubuntu1_all.deb ... Unpacking postgis-doc (3.2.0+dfsg-1ubuntu1) ... Selecting previously unselected package postgresql-14-postgis-3-scripts. Preparing to unpack .../46-postgresql-14-postgis-3-scripts_3.2.0+dfsg-1ubuntu1_all.deb ... Unpacking postgresql-14-postgis-3-scripts (3.2.0+dfsg-1ubuntu1) ... Selecting previously unselected package postgresql-14-postgis-3. Preparing to unpack .../47-postgresql-14-postgis-3_3.2.0+dfsg-1ubuntu1_amd64.deb ... Unpacking postgresql-14-postgis-3 (3.2.0+dfsg-1ubuntu1) ... Selecting previously unselected package proj-bin. Preparing to unpack .../48-proj-bin_8.2.1-1_amd64.deb ... Unpacking proj-bin (8.2.1-1) ... Setting up libgeos3.10.2:amd64 (3.10.2-1) ... Setting up libaom3:amd64 (3.3.0-1) ... Setting up libxerces-c3.2:amd64 (3.2.3+debian-3ubuntu0.1) ... Setting up proj-data (8.2.1-1) ... Setting up libogdi4.1 (4.1.0+ds-5) ... Setting up libcharls2:amd64 (2.3.4-1) ... Setting up libminizip1:amd64 (1.1-8build1) ... Setting up libarpack2:amd64 (3.8.0-1) ... Setting up libsuperlu5:amd64 (5.3.0+dfsg1-2) ... Setting up libqhull-r8.0:amd64 (2020.2-4) ... Setting up libproj22:amd64 (8.2.1-1) ... Setting up postgresql-14-postgis-3-scripts (3.2.0+dfsg-1ubuntu1) ... update-alternatives: using /usr/share/postgresql/14/extension/postgis-3.control to provide /usr/share/postg resql/14/extension/postgis.control (postgresql-14-postgis.control) in auto mode Setting up libprotobuf-c1:amd64 (1.3.3-1ubuntu2.1) ... Setting up libaec0:amd64 (1.0.6-1) ... Setting up gdal-data (3.4.1+dfsg-1build4) ... Setting up libgeotiff5:amd64 (1.7.0-2build1) ... Setting up libsnappy1v5:amd64 (1.1.8-1build3) ... Setting up libcfitsio9:amd64 (4.0.0-1) ... Setting up postgis-doc (3.2.0+dfsg-1ubuntu1) ... Setting up libgmpxx4ldbl:amd64 (2:6.2.1+dfsg-3ubuntu1) ... Setting up libgeos-c1v5:amd64 (3.10.2-1) ... Setting up unixodbc-common (2.3.9-5) ... Setting up libhdf4-0-alt (4.2.15-4) ... Setting up libx265-199:amd64 (3.5-2) ... Setting up libboost-serialization1.74.0:amd64 (1.74.0-14ubuntu3) ... Setting up libodbc2:amd64 (2.3.9-5) ... Setting up liburiparser1:amd64 (0.9.6+dfsg-1) ... Setting up librttopo1:amd64 (1.1.0-2) ... Setting up libfreexl1:amd64 (1.0.6-1) ... Setting up libfyba0:amd64 (4.1.1-7) ... Setting up libkmlbase1:amd64 (1.3.0-9) ... Setting up libblosc1:amd64 (1.21.1+ds2-2) ... Setting up libsfcgal1 (1.4.1-1) ... Setting up libdav1d5:amd64 (0.9.2-1) ... Setting up libde265-0:amd64 (1.0.8-1ubuntu0.1) ... Setting up libsz2:amd64 (1.0.6-1) ... Setting up libkmldom1:amd64 (1.3.0-9) ... Setting up libspatialite7:amd64 (5.0.1-2build2) ... Setting up libodbcinst2:amd64 (2.3.9-5) ... Setting up libarmadillo10 (1:10.8.2+dfsg-1) ... Setting up libkmlengine1:amd64 (1.3.0-9) ... Setting up libheif1:amd64 (1.12.0-2build1) ... Setting up proj-bin (8.2.1-1) ... Setting up libhdf5-103-1:amd64 (1.10.7+repack-4ubuntu2) ... Setting up libhdf5-hl-100:amd64 (1.10.7+repack-4ubuntu2) ... Setting up libnetcdf19:amd64 (1:4.8.1-1) ... Setting up libgdal30 (3.4.1+dfsg-1build4) ... Setting up postgresql-14-postgis-3 (3.2.0+dfsg-1ubuntu1) ... Setting up postgis (3.2.0+dfsg-1ubuntu1) ... Processing triggers for postgresql-common (238) ... Building PostgreSQL dictionaries from installed myspell/hunspell packages... en_us Removing obsolete dictionary files: Processing triggers for libc-bin (2.35-0ubuntu3.6) ... Processing triggers for man-db (2.10.2-1) ... |
You install the postgresql-14-pgrouting libraries:
sudo apt install -y postgresql-14-pgrouting |
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: postgresql-14-pgrouting-scripts Suggested packages: postgresql-14-pgrouting-doc The following NEW packages will be installed: postgresql-14-pgrouting postgresql-14-pgrouting-scripts 0 upgraded, 2 newly installed, 0 to remove and 6 not upgraded. Need to get 705 kB of archives. After this operation, 4,314 kB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgresql-14-pgrouting-scripts all 3.3.0-2 [46.3 kB] Get:2 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgresql-14-pgrouting amd64 3.3.0-2 [659 kB] Fetched 705 kB in 1s (721 kB/s) Selecting previously unselected package postgresql-14-pgrouting-scripts. (Reading database ... 250431 files and directories currently installed.) Preparing to unpack .../postgresql-14-pgrouting-scripts_3.3.0-2_all.deb ... Unpacking postgresql-14-pgrouting-scripts (3.3.0-2) ... Selecting previously unselected package postgresql-14-pgrouting. Preparing to unpack .../postgresql-14-pgrouting_3.3.0-2_amd64.deb ... Unpacking postgresql-14-pgrouting (3.3.0-2) ... Setting up postgresql-14-pgrouting-scripts (3.3.0-2) ... Setting up postgresql-14-pgrouting (3.3.0-2) ... Processing triggers for postgresql-common (238) ... Building PostgreSQL dictionaries from installed myspell/hunspell packages... en_us Removing obsolete dictionary files: |
You should also install ogr2ogr program, which is a command-line utility for converting data between GIS data formats, including common file formats and common spatial databases. You install the ogr2ogr libraries:
sudo apt install -y gdal-bin |
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: python3-gdal python3-numpy Suggested packages: libgdal-grass python-numpy-doc python3-pytest The following NEW packages will be installed: gdal-bin python3-gdal python3-numpy 0 upgraded, 3 newly installed, 0 to remove and 11 not upgraded. Need to get 4,381 kB of archives. After this operation, 24.9 MB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 python3-numpy amd64 1:1.21.5-1ubuntu22.04.1 [3,467 kB] Get:2 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 python3-gdal amd64 3.4.1+dfsg-1build4 [673 kB] Get:3 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 gdal-bin amd64 3.4.1+dfsg-1build4 [241 kB] Fetched 4,381 kB in 1s (2,965 kB/s) Selecting previously unselected package python3-numpy. (Reading database ... 249006 files and directories currently installed.) Preparing to unpack .../python3-numpy_1%3a1.21.5-1ubuntu22.04.1_amd64.deb ... Unpacking python3-numpy (1:1.21.5-1ubuntu22.04.1) ... Selecting previously unselected package python3-gdal. Preparing to unpack .../python3-gdal_3.4.1+dfsg-1build4_amd64.deb ... Unpacking python3-gdal (3.4.1+dfsg-1build4) ... Selecting previously unselected package gdal-bin. Preparing to unpack .../gdal-bin_3.4.1+dfsg-1build4_amd64.deb ... Unpacking gdal-bin (3.4.1+dfsg-1build4) ... Setting up python3-numpy (1:1.21.5-1ubuntu22.04.1) ... Setting up python3-gdal (3.4.1+dfsg-1build4) ... Setting up gdal-bin (3.4.1+dfsg-1build4) ... Processing triggers for man-db (2.10.2-1) ... |
Verify the installation by using the which utility, like
which -a ogr2ogr |
It should return:
/usr/bin/ogr2ogr |
You can qualify the installed PostGIS packages with the following command:
dpkg -l | grep -i postgis |
It should display:
ii postgis 3.2.0+dfsg-1ubuntu1 amd64 Geographic objects support for PostgreSQL ii postgis-doc 3.2.0+dfsg-1ubuntu1 all Geographic objects support for PostgreSQL -- documentation ii postgresql-14-pgrouting 3.3.0-2 amd64 Routing functionality support for PostgreSQL/PostGIS ii postgresql-14-pgrouting-scripts 3.3.0-2 all Routing functionality support for PostgreSQL/PostGIS - SQL scripts ii postgresql-14-postgis-3 3.2.0+dfsg-1ubuntu1 amd64 Geographic objects support for PostgreSQL 14 ii postgresql-14-postgis-3-scripts 3.2.0+dfsg-1ubuntu1 all Geographic objects support for PostgreSQL 14 -- SQL scripts |
Connect as the postgres user by becoming the root user with this command:
sudo sh |
Then, assume the role of the postgres user with this command:
su - postgres |
Connect to the PostgreSQL database as the privileged postgres owner/user:
psql postgres |
You will see the following prompt after connecting to the PostgreSQL database:
postgres@student-virtual-machine:~$ psql psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1)) Type "help" for help. postgres=# |
As the the privileged postgres owner/user issue the following commands to create the gisdb database and set a new search path for it:
CREATE DATABASE gisdb; ALTER DATABASE gisdb SET search_path=public,postgis,contrib,tiger; |
Connect to the gisdb database:
\connect gisdb |
You are now connected to database gisdb as the postgres user. You change to the postgis schema, and create the following extensions in this schema.
CREATE SCHEMA postgis; CREATE EXTENSION postgis SCHEMA postgis; CREATE EXTENSION postgis_raster SCHEMA postgis; CREATE EXTENSION fuzzystrmatch SCHEMA postgis; CREATE EXTENSION address_standardizer_data_us SCHEMA postgis; |
You query the modified catalog with this query:
SELECT n.nspname AS "Name" , pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner" FROM pg_catalog.pg_namespace n WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ORDER BY 1; |
It should return:
Name | Owner ---------+---------- postgis | postgres public | postgres (2 rows) |
You must assign the postgis_tiger_geocoder and postgis_topology without a schema assignment. The
postgis_tiger_eeocoder must be assigned by default to the tiger schema, and the postgis_topology schema.
CREATE EXTENSION postgis_tiger_geocoder; CREATE EXTENSION postgis_topology; |
You reuse the same above referenced query to see the modified catalog with this query:
It should return:
Name | Owner ------------+---------- postgis | postgres public | postgres tiger | postgres tiger_data | postgres topology | postgres (5 rows) |
Connect as the gisdb database with this command:
\connect gisdb |
You can see the active PostGIS extension with this command:
\dx postgis |
It shows:
List of installed extensions Name | Version | Schema | Description ---------+---------+---------+------------------------------------------------------------ postgis | 3.2.0 | postgis | PostGIS geometry and geography spatial types and functions (1 row) |
Now, you can use this query:
SELECT postgis_full_version(); |
to discover what PostGIS version is installed:
postgis_full_version -------------------------------------------------------------------------------------------------------------------------------------------------------------------- POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.12" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" (1 row) |
Connect back to as the privileged postgres owner/user with this command:
\connect postgres |
Next, check the available databases with this command:
\l |
It should display the following:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
gisdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
videodb | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/student +
| | | | | student=CTc/student +
| | | | | dba=CTc/student
(5 rows) |
As the privileged postgres owner/user make the following grants with these commands:
GRANT TEMPORARY, CONNECT ON DATABASE gisdb TO PUBLIC; GRANT ALL PRIVILEGES ON DATABASE gisdb TO postgres; GRANT ALL PRIVILEGES ON DATABASE gisdb TO dba; |
After making the grants, check the available databases access with this \l command:
\l |
It should display the following:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
gisdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | dba=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
videodb | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/student +
| | | | | student=CTc/student +
| | | | | dba=CTc/student
(5 rows) |
At this point, you can exit psql, the postgres user’s account, and the root user’s account. This should return you to your sudoer account, which in my case is the student user.
Connect to the gisdb with the following command-line interface command:
psql -U student -W -d gisdb |
You can create the following, as per instructions in PostGIS Chapter 9 instructions:
-- Conditionally drop table. DROP TABLE IF EXISTS geometries; -- Create table with geometry column in table. CREATE TABLE geometries ( geometries_id INT , name VARCHAR , geometry_obj GEOMETRY); INSERT INTO geometries ( name , geometry_obj ) VALUES ('Point', 'POINT(0 0)') ,('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)') ,('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') ,('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))') ,('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))'); SELECT name , ST_AsText(geometry_obj) FROM geometries; |
Unfortunately, it raises the following error when attempting to create the geometries table:
psql:/home/student/Code/postgis/geometry.sql:7: ERROR: type "geometry" does not exist LINE 4: , geometry_obj GEOMETRY); |
As always, I hope the solutions presented helps move forward implementations of the technology. You can also find an excellent tutorial to learning PostGIS in the Introduction to PostGIS tutorial.
Configure PostgreSQL 14
After you install PostgreSQL 14 on Windows, there are a few steps to create a sandbox database. This post shows you those steps, including a couple Windows OS tasks you’ll need to complete. You should note that these instructions are for the PostgreSQL psql Command Line Interface (CLI).
Open a Command Prompt with Administrator privileges. It should give you a command prompt like the following:
Microsoft Windows [Version 10.0.19042.1466] (c) Microsoft Corporation. All rights reserved. C:\Users\username> |
Type psql to launch the PostgreSQL CLI and then the return or enter key:
C:\Users\username>psql |
Most likely, you’ll get the following error message. It means that your System Path environment variable doesn’t include the directory where the psql executable is found, and that’s why the command is not recognized.
'psql' is not recognized as an internal or external command, operable program or batch file. |
You can set it temporarily in your current Windows CLI with the following command:
set PATH=%PATH%;C:\Program Files\PostgreSQL\14\bin; |
For those familiar with Windows CLI navigation in prior releases, the convention is to append a semicolon at the end of the item added to the %PATH% environment variable. If you were to put the semicolon between the %PATH% and new directory path there would be two semicolons together. While it won’t do any harm, it’s best to follow the new convention or style.
CRITICAL NOTE: The rest of the post assumes you have set the correct %PATH% environment variable or added it to your System’s Path environment variable and restarted the Windows CLI after adding it through the GUI tool. The reason you need to restart the Windows CLI is that the %PATH% environment variable is inherited at startup and doesn’t change in an open Windows CLI shell.
Another common mistake some users make, at least those who have used an older version of the psql utility on a Linux distribution (or “distro”), is to type psql without any arguments to become the superuser. This error doesn’t occur in Linux because you most likely connected as the postgres user before trying to connect to the PostgreSQL database. A quick demonstration should illustrate the error and support explaining why it occurs on the Windows OS.
Attempting to connect to the PostgreSQL database as a non-postgres user:
C:\Users\username>psql |
You should get the following error:
psql: error: connection to server at "localhost" (::1), port 5432 failed: fe_sendauth: no password supplied |
This error occurs because you’re not the postgres user, and all other users must designate that they’re connecting to the superuser account. The correct syntax is:
C:\Users\username>psql -U postgres |
Then, you’ll be prompted for the password that you set when you installed PostreSQL database. Enter that password from the installation at the prompt.
Password for user postgres:
psql (14.1)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# |
The warning message is telling you that the character set collation differs between your Windows OS and the PostgreSQL database. We’ll discuss this more later but for the sake of almost all your work, it won’t matter. If the warning message bothers you, you can run the chcp command before launching PostgreSQL when you open your Windows CLI:
chcp 1252 |
The chcp command changes your terminal character set to align with the Latin 1 character set, which enables you to use things like non-English accent characters (the umlaut over an o, or ö). After running the You will see this when you connect after running that command:
psql (14.2) Type "help" for help. postgres# |
INFO: The chcp command is used to supplement the international keyboard and character set information, allowing MS-DOS to be used in other countries and with different languages. Before the chcp command can be used, the nlsfunc must be loaded, and the country.sys must be loaded into the OS configuration.
If you are an experienced Windows OS user, you may want to edit your Windows Registry to change this behavior automatically for each Windows CLI session. You can do that by opening the Windows Registry with the regedit command as an Administrator. In regedit, add an Autorun element with a value of chcp 1252 to this part of the registry:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Command Processor |
This value automatically sets your Windows CLI to a Latin 1 character set everytime you launch a terminal shell with the cmd.exe utility. You should only do this if you understand it completely.
Now that you’re connected as the superuser, let’s examine the steps to configure your playground or videodb database. There are five steps to configure your database and one step to connect and use the videodb database as the student user
- Create Physical Directory
The directory for the data dictionary changes with PostgreSQL installations. You can find it with the following psql CLI command:
postgres=# show data_directory; |
This will return the following:
data_directory ------------------------------------- C:/Program Files/PostgreSQL/14/data (1 row) |
While it is possible to store your subdirectory in the data dictionary, it will raise a warning message. It’s actually a better practice to install your local databases in another location on your file system.
Open a new Windows OS CLI to create a subdirectory (or in Windows parlance folder) where you will store your videoDB database. Each new Windows OS CLI opens in your home directory. You need to create a physical video_db subdirectory in your home directory.
HINT: The name of the database inside PostgreSQL should map to the tablespace name and differ from the physical directory. Otherwise there is a chance you might get confused and make an error in the future.
In a new command line shell, you can use the following syntax to create videoDB subdirectory:
md video_db |
- Create Tablespace
Returning to the original Windows CLI shell where you are connected as the postgres superuser, you can create a video_db tablespace with the following syntax:
CREATE TABLESPACE video_db OWNER postgres LOCATION 'C:\Users\username\video_db'; |
This will return the following:
CREATE TABLESPACE |
You can query whether you successfully create the video_db tablespace with the following:
SELECT * FROM pg_tablespace; |
It should return the following:
oid | spcname | spcowner | spcacl | spcoptions -------+------------+----------+--------+------------ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 16395 | video_db | 10 | | (3 rows) |
- Create a Database
You need to know the PostgreSQL default collation before you create a new database. You can write the following query to determine the default correlation:
postgres=# SELECT datname, datcollate FROM pg_database WHERE datname = 'postgres'; |
It should return something like this:
datname | datcollate -----------+---------------------------- postgres | English_United States.1252 (1 row) |
The datcollate value of the postgres database needs to the same value for the LC_COLLATE and LC_CTYPE parameters when you create a database. You can create a videodb database with the following syntax provided you’ve made appropriate substitutions for the LC_COLLATE and LC_CTYPE values below:
CREATE DATABASE videodb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = video_db LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' CONNECTION LIMIT = -1; |
You can verify the creation of the videodb with the following command:
postgres# \l |
It should show you a display like the following:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+----------------------------+----------------------------+-----------------------
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
videodb | postgres | UTF8 | English_United States.1252 | English_United States.1252 |
(4 rows) |
Then, you can assign comment to the database with the following syntax:
COMMENT ON DATABASE videodb IS 'Video Store Database'; |
- Create a Role, Grant, and User
In this section you create a dba role, grant privileges on a videodb database to a role, and create a user with the role that you created previously with the following three statements. There are three steps in this sections.
- The first step creates a dba role:
CREATE ROLE dba WITH SUPERUSER;
- The second step grants all privileges on the videodb database to both the postgres superuser and the dba role:
GRANT ALL PRIVILEGES ON DATABASE videodb TO postgres; GRANT ALL PRIVILEGES ON DATABASE videodb TO dba;
Any work in pgAdmin4 requires a grant on the videodb database to the postgres superuser. The grant enables visibility of the videodb database in the pgAdmin4 console as shown in the following image.
- The third step creates a student user with the dba role:
CREATE USER student WITH ROLE dba ENCRYPTED PASSWORD 'student';
After this step, you need to disconnect as the postgres superuser with the following command:
QUIT;
- It is possible that you may (and should if this is a new instance you are building) encounter an error when you try to connect as a sandboxed user. The syntax to connect as the student user is:
psql -d videodb -U student -W
All the options, which are preceded with a single dash (–) are case sensitive. The -d option sets the database for the connection. The -U option set user for the connection and the -W option instructs the psql CLI to prompt for the password.
While you shouldn’t encounter the following error during a Windows OS installation,
psql: FATAL: Peer authentication failed for user "student"
You can fix this in PostgreSQL 14 by changing the user access parameters in the pg_hba.conf configuration file. The file is found in the C:\Program Files\PostgreSQL\14\data directory. These are the correct out of the box settings you should see.
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all scram-sha-256 # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all scram-sha-256 host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256
If you find something that’s broken, fix it. The values above should ensure you make the changes successfully. You will need to restart the postgres service if you make changes.
If you plan on using the copy command to read external CSV (Comma Separated Value) files, you need to grant as the postgres superuser another privilege to the student user. This grants the pg_read_server_files role to the student user.
GRANT pg_read_server_files TO student;
- Create a Schema
The PostgreSQL database supports multiple schemas inside databases. The default schema for any database is the public schema. You must create separate schemas and refer to them explicitly when accessing them unless you alter the default search path. This section demonstrates how to:
- Create an app schema.
- Create a revision_history table in the app schema.
- Modify the standard search path to include other schemas
The process of creating a schema requires you grant the CREATE ON DATABASE privilege to the user as the postgres user. The initial grant of the create privilege requires the postgres superuser’s privileges. That means you must connect as the postgres user, like:
psql -U postgres -W |
Then, as the postgres superuser, you use the following syntax to grant the create privilege to the student user on the videodb database:
GRANT CREATE ON DATABASE videodb TO student; |
After granting the create privilege, you should exit the postgres superuser’s account, like
QUIT; |
Now, you should connect as the student user to the videodb database (syntax introduced earlier but provided again below).
psql -U postgres -W |
As the student user, create the app schema with the following syntax:
CREATE SCHEMA app; |
Then, you can query the result as follows:
SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname; |
You should see the following:
oid | nspname | nspowner | nspacl
-------+--------------------+----------+-------------------------------------
16399 | app | 16398 |
13388 | information_schema | 10 | {postgres=UC/postgres,=U/postgres}
11 | pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
99 | pg_toast | 10 |
2200 | public | 10 | {postgres=UC/postgres,=UC/postgres}
(5 rows) |
If you create a table without a schema name, it is automatically placed in the public schema. That means any attempt to describe the table with the \d command line option returns without prepending the schema name returns an error, however, this is only true when you are using the default search parameter.
Let’s create a revision_history table in the app schema with a script file. A script file is a set of related SQL commands, like the following example that suppresses notices, drops any pre-existing revision_history table, and create the revision_history table.
-- Set client messages to warning or error, which means any -- notice associated with the if exists clause is suppressed. SET client_min_messages TO warning; -- Conditionally drop an existing revision_history table in -- the app schema. DROP TABLE IF EXISTS revision_history; -- Create a revision_history table. CREATE TABLE app.revision_history ( revision_history_id serial , session_id VARCHAR , table_name VARCHAR , revision_id INTEGER ); |
You can run a script file by using a relative or absolute file name. An absolute file name includes a full path from a Windows logical driver letter, like C:\ or a Linux mount point. A relative file name is simply the file name.
If you want to use a relative file name, you must first navigate to the directory where you have saved the file first. This directory becomes your local drive and allows you call any file in it from the psql command prompt by using only its file name.
You should connect as the student user to the videodb database. The \i command lets you run a file, assuming you put the preceding script file into a revision_history file in your local directory.
\I revision_history.sql |
If you try to describe the revision_history table with the \d command line option, like
\d revision_history |
It will show the following:
Did not find any relation named "revision_history". |
That’s because there is no revision_history table in the public schema and the default search path only includes the public schema.
You can show the search path with the following:
show search_path; |
It should return the following, which is a schema that shares the user’s name and public.
search_path ----------------- "$user", public (1 row) |
You reset the search path as follows:
SET search_path TO app, "$user", public; |
After you set the search_path, an attempt to describe the table will work because it searches for the table in the app and public schema. That means the following command:
\d revision_history |
Shows:
Table "app.revision_history"
Column | Type | Collation | Nullable | Default
---------------------+-------------------+-----------+----------+---------------------------------------------------------------
revision_history_id | integer | | not null | nextval('revision_history_revision_history_id_seq'::regclass)
session_id | character varying | | |
table_name | character varying | | |
revision_id | integer | | | |
- Connect as student to videodb:
As shown in Step #4 above, you can now connect and use to the videodb as the student user with the following syntax:
psql -d videodb -U student -W |
If you did everything correctly, you should see the following after correctly providing the student password for the student user:
Password:
psql (14.1)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
videodb=> |
After connecting to the videodb database, you can query the current database, like
SELECT current_database(); |
It should return the following:
current_database ------------------ videodb (1 row) |
This has shown you how to create a videodb tablespace, a videodb database, a dba role, a student user, an app schema, and connect to your new videodb database as the student user. As always, I hope it lets you get a lot down with little effort and avoiding pages and pages of documentation.
pgAdmin4 on Fedora 30
While attempting an install of pgAdmin and updating a Fedora 30 environment, I encountered a conflict on the upgrade of MySQL 8.0.17-1 to 8.0.17.2. The community-mysql-8.0.17-2.fc30.x86_64 had conflicts with:
mysql-community-client-8.0.17-1.fc30.x86_64packagemysql-community-server-8.0.17-1.fc30.x86_64package
I tried to update the system before install pgadmin4 with the following syntax:
dnf -y update && dnf -y install pgadmin4 |
The dnf utility raise the following MySQL package errors during transaction checking:
Display detailed console log →
Error: Transaction check error: file /usr/bin/mysql conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysql_config_editor conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqladmin conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlbinlog conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlcheck conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqldump conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlimport conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlpump conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlshow conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/mysqlslap conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64 file /usr/bin/ibd2sdi conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/innochecksum conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/my_print_defaults conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/myisam_ftdump conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/myisamchk conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/myisamlog conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/myisampack conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/mysql_secure_installation conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/mysql_ssl_rsa_setup conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/mysql_tzinfo_to_sql conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/mysql_upgrade conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/mysqld_pre_systemd conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/bin/perror conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib/systemd/system/mysqld.service conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib/systemd/system/mysqld@.service conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/adt_null.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/auth_socket.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/component_audit_api_message_emit.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/component_log_filter_dragnet.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/component_log_sink_json.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/component_log_sink_syseventlog.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/component_validate_password.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/connection_control.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/ddl_rewriter.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/group_replication.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/ha_example.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/ha_mock.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/innodb_engine.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/keyring_file.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/keyring_udf.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/libmemcached.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/locking_service.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/mypluglib.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/mysql_clone.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/mysql_no_login.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/rewrite_example.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/rewriter.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/semisync_master.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/semisync_slave.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/validate_password.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/plugin/version_token.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/sbin/mysqld conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /var/lib/mysql conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /var/lib/mysql-keyring conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64 file /usr/lib64/mysql/libmysqlclient.so.21.1.17 conflicts between attempted installs of community-mysql-libs-8.0.17-2.fc30.x86_64 and mysql-community-libs-8.0.17-1.fc30.x86_64 |
Since I’m not sure what’s wrong or how to fix it, I’ve put it in my queue of things to get to later. However, when I figure it out I’ll update this blog page with the solution or work around. If anybody knows the fix and would like to share, please let me know.
I removed the pending update packages with the following command:
dnf clean packages |
Then, I simply installed pgadmin4 with the following command:
dnf -y install pgadmin4 |
Display detailed console log →
The pgadmin4 configuration instructions can be found for several Linux versions at Josphat Mutai’s Computing for Geeks web page. On Fedora 30, you need to do the following:
- Install, start, and enable Apache as the
httpdservice unless you already have done that. - Copy the
/etc/httpd/conf.d/pgadmin4.conf.samplefile to/etc/httpd/conf.d/pgadmin4.conf, which is a new file. - Restart the
httpdservice to incorporate thepgadmin4configuration file.
After that, you create the following new directories as the root or sudo user:
/var/lib/pgadmin4/var/log/pgadmin4
You can make both directories with a single mkdir command, like:
mkdir -p /var/lib/pgadmin4 /var/log/pgadmin4 |
As the root or sudo user, change the ownership of these two directories to the apache user with the following syntax:
chown -R apache:apache /var/lib/pgadmin4 /var/log/pgadmin4 |
You add the following four statements to the config_distro.py file in the /usr/lib/python3.7/site-packages/pgadmin4-web directory as the root or sudo user:
LOG_FILE = '/var/log/pgadmin4/pgadmin4.log' SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db' SESSION_DB_PATH = '/var/lib/pgadmin4/sessions' STORAGE_DIR = '/var/lib/pgadmin4/storage' |
You need to setup the pgadmin user with the following python3 command:
python3 /usr/lib/python3.7/site-packages/pgadmin4-web/setup.py |
Enter the following values, a real email address and a password twice:
NOTE: Configuring authentication for SERVER mode. Enter the email address and password to use for the initial pgAdmin user account: Email address: admin@example.com Password: your_password Retype password: your_password pgAdmin 4 - Application Initialisation ====================================== |
Assuming you have an enabled firewall, you need to issue the following two commands as the root or sudo user:
rirewall-cmd --permanent --add-service=http firewall-cmd --reload |
You invoke pgAdmin4 from within a browser window with the following URL for a stand alone workstation (for a workstation on a DNS network you would enter pgadmin.domain.domain_type in lieu of localhost):
pgadmin/localhost/pgadmin4 |
You most likely will encounter an Internal Server Error, the recommended fix is reputed to be:
ausearch -c 'httpd' --raw | audit2allow -M my-httpd semodule -X 300 -i my-httpd.pp |
It didn’t work for me. At the end of the process, I have an Internal Server Error. It is something that I’ll try to fix next. The actual error message:
Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator at root@localhost to inform them of the time this error occurred, and the actions you performed just before this error. More information about this error may be available in the server error log. |
If somebody figures out the last step before I do, that’s great. Let me and everybody else know the mystery.
On a positive note, the pgadmin4 package provided the psycopg2 library. I had looked for it as a psycopg2 package but it is in python3-psycopg2 package.
PostgreSQL on Fedora 30
Installing PostreSQL 11 on Fedora 30 wasn’t straight forward but there were some instructions that helped. The first step requires you to update the yum repository, like this as the root user:
rpm -Uvh https://yum.postgresql.org/11/fedora/fedora-30-x86_64/pgdg-fedora-repo-latest.noarch.rpm |
Then, you install the PostgreSQL with this command as the root user:
dnf install postgresql11-server |
After installing the PostreSQL Server I got a few errors with the symbolic links failing to resolve in the log files. Then, I realized they only failed to create symbolic links because the fresh installation deploys executables directly to the /usr/bin directory.
Display detailed console log →
Retrieving https://yum.postgresql.org/11/fedora/fedora-30-x86_64/pgdg-fedora-repo-latest.noarch.rpm warning: /var/tmp/rpm-tmp.MD4lRU: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY Verifying... ################################# [100%] Preparing... ################################# [100%] Updating / installing... 1:pgdg-fedora-repo-42.0-4 ################################# [100%] [root@localhost ~]# dnf install postgresql11-server PostgreSQL 11 30 - x86_64 215 kB/s | 585 kB 00:02 PostgreSQL 10 30 - x86_64 199 kB/s | 541 kB 00:02 PostgreSQL 9.6 30 - x86_64 295 kB/s | 515 kB 00:01 PostgreSQL 9.5 30 - x86_64 179 kB/s | 495 kB 00:02 PostgreSQL 9.4 30 - x86_64 269 kB/s | 469 kB 00:01 Last metadata expiration check: 0:00:01 ago on Mon 19 Aug 2019 02:25:56 AM MDT. Dependencies resolved. ================================================================================================================== Package Architecture Version Repository Size ================================================================================================================== Installing: postgresql11-server x86_64 11.5-1PGDG.f30 pgdg11 4.8 M Installing dependencies: postgresql11 x86_64 11.5-1PGDG.f30 pgdg11 1.7 M postgresql11-libs x86_64 11.5-1PGDG.f30 pgdg11 374 k Transaction Summary ================================================================================================================== Install 3 Packages Total download size: 6.9 M Installed size: 32 M Is this ok [y/N]: y Downloading Packages: (1/3): postgresql11-libs-11.5-1PGDG.f30.x86_64.rpm 213 kB/s | 374 kB 00:01 (2/3): postgresql11-11.5-1PGDG.f30.x86_64.rpm 698 kB/s | 1.7 MB 00:02 (3/3): postgresql11-server-11.5-1PGDG.f30.x86_64.rpm 1.5 MB/s | 4.8 MB 00:03 ------------------------------------------------------------------------------------------------------------------ Total 2.2 MB/s | 6.9 MB 00:03 warning: /var/cache/dnf/pgdg11-cde8ad453ae6cd5b/packages/postgresql11-11.5-1PGDG.f30.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY PostgreSQL 11 30 - x86_64 1.6 MB/s | 1.7 kB 00:00 Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>" Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]: y Key imported successfully Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : postgresql11-libs-11.5-1PGDG.f30.x86_64 1/3 Running scriptlet: postgresql11-libs-11.5-1PGDG.f30.x86_64 1/3 Installing : postgresql11-11.5-1PGDG.f30.x86_64 2/3 Running scriptlet: postgresql11-11.5-1PGDG.f30.x86_64 2/3 failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql exists and it is not a symlink failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb: /usr/bin/clusterdb exists and it is not a symlink failed to link /usr/bin/createdb -> /etc/alternatives/pgsql-createdb: /usr/bin/createdb exists and it is not a symlink failed to link /usr/bin/createuser -> /etc/alternatives/pgsql-createuser: /usr/bin/createuser exists and it is not a symlink failed to link /usr/bin/dropdb -> /etc/alternatives/pgsql-dropdb: /usr/bin/dropdb exists and it is not a symlink failed to link /usr/bin/dropuser -> /etc/alternatives/pgsql-dropuser: /usr/bin/dropuser exists and it is not a symlink failed to link /usr/bin/pg_dump -> /etc/alternatives/pgsql-pg_dump: /usr/bin/pg_dump exists and it is not a symlink failed to link /usr/bin/pg_dumpall -> /etc/alternatives/pgsql-pg_dumpall: /usr/bin/pg_dumpall exists and it is not a symlink failed to link /usr/bin/pg_restore -> /etc/alternatives/pgsql-pg_restore: /usr/bin/pg_restore exists and it is not a symlink failed to link /usr/bin/reindexdb -> /etc/alternatives/pgsql-reindexdb: /usr/bin/reindexdb exists and it is not a symlink failed to link /usr/bin/vacuumdb -> /etc/alternatives/pgsql-vacuumdb: /usr/bin/vacuumdb exists and it is not a symlink Running scriptlet: postgresql11-server-11.5-1PGDG.f30.x86_64 3/3 Installing : postgresql11-server-11.5-1PGDG.f30.x86_64 3/3 Running scriptlet: postgresql11-server-11.5-1PGDG.f30.x86_64 3/3 Verifying : postgresql11-11.5-1PGDG.f30.x86_64 1/3 Verifying : postgresql11-libs-11.5-1PGDG.f30.x86_64 2/3 Verifying : postgresql11-server-11.5-1PGDG.f30.x86_64 3/3 Installed: postgresql11-server-11.5-1PGDG.f30.x86_64 postgresql11-11.5-1PGDG.f30.x86_64 postgresql11-libs-11.5-1PGDG.f30.x86_64 Complete! |
After installing the PostgreSQL Server 11, you need to initialize the database. You use the following command to initialize the database as the root user:
/usr/pgsql-11/bin/postgresql-11-setup initdb |
It should return the following:
Initializing database ... OK |
The PostgreSQL Server 11 database installs in the /var/lib/pgsql/11/data directory. You can list the contents, which should mirror these:
drwx------. 5 postgres postgres 4096 Aug 19 02:45 base drwx------. 2 postgres postgres 4096 Aug 19 02:45 global drwx------. 2 postgres postgres 4096 Aug 19 02:45 log drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_commit_ts drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_dynshmem -rw-------. 1 postgres postgres 4269 Aug 19 02:45 pg_hba.conf -rw-------. 1 postgres postgres 1636 Aug 19 02:45 pg_ident.conf drwx------. 4 postgres postgres 4096 Aug 19 02:45 pg_logical drwx------. 4 postgres postgres 4096 Aug 19 02:45 pg_multixact drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_notify drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_replslot drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_serial drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_snapshots drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_stat drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_stat_tmp drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_subtrans drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_tblspc drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_twophase -rw-------. 1 postgres postgres 3 Aug 19 02:45 PG_VERSION drwx------. 3 postgres postgres 4096 Aug 19 02:45 pg_wal drwx------. 2 postgres postgres 4096 Aug 19 02:45 pg_xact -rw-------. 1 postgres postgres 88 Aug 19 02:45 postgresql.auto.conf -rw-------. 1 postgres postgres 23895 Aug 19 02:45 postgresql.conf |
You need to enable and start the postgresql-11.service with the following commands as the root user:
systemctl enable postgresql-11.service systemctl start postgresql-11.service |
You can login to test the configuration as the root user, like this:
su - postgres -c "psql" |
You will see something like this:
psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql) psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql) psql (11.4, server 11.5) Type "help" for help. postgres=# |
The error message appear to indicate there’s a bug (at least Bug #15798 is similar). Specifically, a missing function in the libya.so.5 library. Determining that impact took some time because of what else I had in the queue.
The Bug (at least Bug #15798 gave part of the fix. The problem was figuring out where the LD_LIBRARY_PATH should really be set, and I sorted that out.
If you inspect the postgres home directory (/var/lib/pgsql), you’ll find the following .bash_profile file:
[ -f /etc/profile ] && source /etc/profile PGDATA=/var/lib/pgsql/11/data export PGDATA # If you want to customize your settings, # Use the file below. This is not overridden # by the RPMS. [ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile |
Then, you create the .pgsql_profile file in that directory. You should put the following command in the file:
export set LD_LIBRARY_PATH=/usr/lib64 needle < /dev/null |
Then, when you login as the postgres user:
psql -U postgres |
You will see:
psql (11.4, server 11.5) Type "help" for help. postgres=# |
or, you can login to test the configuration as the root user with the syntax used earlier:
su - postgres -c "psql" |
You need to put the LD_LIBRARY_PATH environment variable in the .bashrc of users who will access the PostgreSQL 11 database.
As always, I hope this helps those working it from the ground up.
PostgreSQL Install-Windows
A number of folks have asked me to create a post of a PostgreSQL installation on Windows. So, here is my step-by-step PostgreSQL 9.3 installation, and you can go further and learn how to setup your own PostgreSQL database schema.
Download PostgreSQL Database
You can start the download of PostgreSQL from this site. It leads you to the EnterpriseDB site, where you can download the prepackaged software.
Install PostgreSQL 9.3 on Windows
These steps walk you through installing PostgreSQL and the the pgJDBC v9.3, psqlODBC (32 bit), and psqlODBC (64 bit) packages.
- The first thing you need to do is launch the PostgreSQL file from download folder.
- After launching the downloaded file, you see an Open File – Security Warning dialog message. Click the Run button to run the PostgreSQL executable file.
- The first dialog screen is the Setup – PostgreSQL dialog, which welcomes you and prompts you to start the installtion of PostgreSQL. Click the Next button to continue.
- The Installation Directory dialog sets the location for the PostgreSQL server. Click the Next button to continue.
- The Data Directory dialog sets the location for the PostgreSQL server’s data repository. Click the Next button to continue.
- The first Setup dialog sets the database’s superuser password. Click the Next button to continue.
- The second Setup dialog sets the database’s listener port, which is Port
5432by default. Click the Next button to continue.
- The third Setup dialog sets the database’s default Locale. Click the Next button to continue.
- The Ready to Install dialog lets you start the installation. Click the Next button to continue.
- The Installing dialog lets you montor the unpacking of programs for the installation. You need not click any buttons to continue.
- The Completing the PostgreSQL Setup Wizard dialog advises you that you’ve completed the setup. Checking the Stack Builder check box lets you download and install additional tools, drivers, and applications. Click the Next button to continue.
- This is Welcome dialog to the Stack Builder. Click the Next button to continue.
- This is dialog lets you select applications that you would like to install. Click the plus symbol for the Database Drivers option to continue.
- In the modified dialog, click the pgJDBC v9.3, psqlODBC (32 bit), and psqlODBC (64 bit) check boxes. Then, click the Next button to continue.
- This dialog tells you the pgJDBC v9.3, psqlODBC (32 bit), and psqlODBC (64 bit) installed packages. Click the Next button to continue.
- This dialog lays a downloading progress bar while loading the additional packages. You don’t need to do anything to continue.
- This dialog lays a downloading progress bar while loading the additional packages. Click the Next button to continue.
- The Setup pgJDBC dialog welcomes you to the setup wizard. Click the Next button to continue.
- The Installation Directory dialog lets you set the pgJDBC installation directory. Click the Next button to continue.
- The Ready to Install dialog lets you install the pgJDBC package. Click the Next button to install it.
- The Setup dialog confirms you’ve installed the add-on packages. Click the Finish button to complete the installation.
Configure psqlODBC on Windows
These steps walk you through configuring the psqlODBC packages.
- The Setup dialog helps you configure the psqlODBC package. Click the Next button to install it.
- The Installation Directory dialog lets you set the psqlODBC installation directory. Click the Next button to continue.
- The Ready to Install dialog lets you install the psqlODBC package. Click the Next button to install it.
- The Ready to Install dialog lets you install the psqlODBC package. Click the Next button to install it.
- The Installing psqlODBC dialog displays a dialog while installing the psqlODBC package(s). You need not click anything, the dialog dismisses itself when complete.
- The Installing psqlODBC dialog displays a dialog while installing the psqlODBC package(s). You need not click anything, the dialog dismisses itself when complete.
- The Stack Builder dialog displays a message that everything is installed. Click the Finish button when complete.
Connect to the Default PostgreSQL database
You have two options for connecting to the PostgreSQL database. One uses the GUI pgAdmin III console and the other uses the command line.
Connect through pgAdmin III to the PostgreSQL Database
- In Windows, navigate to through the Start to the pgAdmin III menu option.
- After launching pgAdmin III, you’ll see the pgAdmin II console, displayed at left.
- Right click on the PostgreSQL 9.3 (localhost:5432) item in the Object browser and click the Connect menu choice from the floating menu.
- Enter the password you chose when installing the PostgreSQL Server, and click the OK button.
Connect through the Command-line to the PostgreSQL Database
These steps show you how to connect through the PostgreSQL Command Line Interface (CLI) – psql. psql is like Oracle’s sqlplus, MySQL’s mysql, and Microsoft SQL Server’s sqlcmd CLI interfaces. By default, the PostgreSQL binary directory is not in your default %PATH% environment variable, so you need to add it. I recommend an environment file because in a test situation you may not want to use it all the time.
Create the following directory for the environment file:
C:\Data\PostgreSQL\env |
You can create an environment file with any text editor or do it at the command line, like this:
COPY CON postgresqlenv.bat SET PATH=%PATH%;C:\Program Files\PostgreSQL\9.3\bin;. ^Z |
Then, you can source the environment file like this:
C:\> postgresql.bat |
Now, you can connect to the default database provided you understand that PostgreSQL Stack Builder set some values that differ from the defaults if you installed it manually. Specifically, they set the default value of the database to postgres and default value of the user to postgres.
That means you can connect with the following, provided you remember the password you used during installation:
C:\> psql -d postgres -U postgres |
You should see the following when you connect:
C:\> psql -d postgres -U postgres Password for user postgres: psql (9.3.3) WARNING: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. Type "help" for help. |
You can now submit interactive SQL statements or run batch files, like:
postgres=# SELECT 'Hello World!'; |
It displays:
?COLUMN? -------------- Hello World! (1 ROW) |
Adding a column alias to the string literal gives a more readable formatted output:
postgres=# SELECT 'Hello World!' AS "String Literal"; |
It displays:
String Literal ---------------- Hello World! (1 ROW) |
PostgreSQL’s CLI Help
You have the ability to do a lot with the PostgreSQL psql CLI. The general command to discover help is help, and it displays the following:
postgres=# help You are USING psql, the command-line interface TO PostgreSQL. TYPE: \copyright FOR distribution terms \h FOR help WITH SQL commands \? FOR help WITH psql commands \g OR terminate WITH semicolon TO EXECUTE query \q TO quit |
You quit PostgreSQL with a \q command:
postgres=# \q |
You can find more help with the \HELP or the \? commands:
\HELP |
It displays the following:
Available help: ABORT CLUSTER DEALLOCATE END ALTER AGGREGATE COMMENT DECLARE EXECUTE ALTER COLLATION COMMIT DELETE EXPLAIN ALTER CONVERSION COMMIT PREPARED DISCARD FETCH ALTER DATABASE COPY DO GRANT ALTER DEFAULT PRIVILEGES CREATE AGGREGATE DROP AGGREGATE INSERT ALTER DOMAIN CREATE CAST DROP CAST LISTEN ALTER EVENT TRIGGER CREATE COLLATION DROP COLLATION LOAD ALTER EXTENSION CREATE CONVERSION DROP CONVERSION LOCK ALTER FOREIGN DATA WRAPPER CREATE DATABASE DROP DATABASE MOVE ALTER FOREIGN TABLE CREATE DOMAIN DROP DOMAIN NOTIFY ALTER FUNCTION CREATE EVENT TRIGGER DROP EVENT TRIGGER PREPARE ALTER GROUP CREATE EXTENSION DROP EXTENSION PREPARE TRANSACTION ALTER INDEX CREATE FOREIGN DATA WRAPPER DROP FOREIGN DATA WRAPPER REASSIGN OWNED ALTER LANGUAGE CREATE FOREIGN TABLE DROP FOREIGN TABLE REFRESH MATERIALIZED VIEW ALTER LARGE OBJECT CREATE FUNCTION DROP FUNCTION REINDEX ALTER MATERIALIZED VIEW CREATE GROUP DROP GROUP RELEASE SAVEPOINT ALTER OPERATOR CREATE INDEX DROP INDEX RESET ALTER OPERATOR CLASS CREATE LANGUAGE DROP LANGUAGE REVOKE ALTER OPERATOR FAMILY CREATE MATERIALIZED VIEW DROP MATERIALIZED VIEW ROLLBACK ALTER ROLE CREATE OPERATOR DROP OPERATOR ROLLBACK PREPARED ALTER RULE CREATE OPERATOR CLASS DROP OPERATOR CLASS ROLLBACK TO SAVEPOINT ALTER OPERATOR CREATE INDEX DROP INDEX RESET ALTER OPERATOR CLASS CREATE LANGUAGE DROP LANGUAGE REVOKE ALTER OPERATOR FAMILY CREATE MATERIALIZED VIEW DROP MATERIALIZED VIEW ROLLBACK ALTER ROLE CREATE OPERATOR DROP OPERATOR ROLLBACK PREPARED ALTER RULE CREATE OPERATOR CLASS DROP OPERATOR CLASS ROLLBACK TO SAVEPOINT ALTER SCHEMA CREATE OPERATOR FAMILY DROP OPERATOR FAMILY SAVEPOINT ALTER SEQUENCE CREATE ROLE DROP OWNED SECURITY LABEL ALTER SERVER CREATE RULE DROP ROLE SELECT ALTER TABLE CREATE SCHEMA DROP RULE SELECT INTO ALTER TABLESPACE CREATE SEQUENCE DROP SCHEMA SET ALTER TEXT SEARCH CONFIGURATION CREATE SERVER DROP SEQUENCE SET CONSTRAINTS ALTER TEXT SEARCH DICTIONARY CREATE TABLE DROP SERVER SET ROLE ALTER TEXT SEARCH PARSER CREATE TABLE AS DROP TABLE SET SESSION AUTHORIZATION ALTER TEXT SEARCH TEMPLATE CREATE TABLESPACE DROP TABLESPACE SET TRANSACTION ALTER TRIGGER CREATE TEXT SEARCH CONFIGURATION DROP TEXT SEARCH CONFIGURATION SHOW ALTER TYPE CREATE TEXT SEARCH DICTIONARY DROP TEXT SEARCH DICTIONARY START TRANSACTION ALTER USER CREATE TEXT SEARCH PARSER DROP TEXT SEARCH PARSER TABLE ALTER USER MAPPING CREATE TEXT SEARCH TEMPLATE DROP TEXT SEARCH TEMPLATE TRUNCATE ALTER VIEW CREATE TRIGGER DROP TRIGGER UNLISTEN ANALYZE CREATE TYPE DROP TYPE UPDATE BEGIN CREATE USER DROP USER VACUUM CHECKPOINT CREATE USER MAPPING DROP USER MAPPING VALUES CLOSE CREATE VIEW DROP VIEW WITH |
You can find more help with the \HELP or the \? commands:
\? |
It displays the following:
General
\copyright show PostgreSQL usage and distribution terms
\g [FILE] or ; execute query (and send results to file or |pipe)
\gset [PREFIX] execute query and store results in psql variables
\h [NAME] help on syntax of SQL commands, * for all commands
\q quit psql
\watch [SEC] execute query every SEC seconds
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\ddp [PATTERN] list default privileges
\dD[S+] [PATTERN] list domains
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dm[S+] [PATTERN] list materialized views
\dn[S+] [PATTERN] list schemas
\do[S] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\drds [PATRN1 [PATRN2]] list per-database role settings
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dE[S+] [PATTERN] list foreign tables
\dx[+] [PATTERN] list extensions
\dy [PATTERN] list event triggers
\l[+] [PATTERN] list databases
\sf[+] FUNCNAME show a function's definition
\z [PATTERN] same as \dp
Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset NAME [VALUE] set table output option
(NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|
numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager})
\t [on|off] show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x [on|off|auto] toggle expanded output (currently off)
Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently "studentdb")
\encoding [ENCODING] show or set client encoding
\password [USERNAME] securely change the password for a user
\conninfo display information about current connection
Operating System
\cd [DIR] change the current working directory
\setenv NAME [VALUE] set or unset environment variable
\timing [on|off] toggle timing of commands (currently off)
\! [COMMAND] execute command in shell or start interactive shell
Variables
\prompt [TEXT] NAME prompt user to set internal variable
\set [NAME [VALUE]] set internal variable, or list all if no parameters
\unset NAME unset (delete) internal variable
Large Objects
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations |
Have fun exploring PostgreSQL. You can click here to learn how to setup your own tablespace, database, and user. As always, I hope this helps those looking to discover how to install and use PostgreSQL.
Subscribe via RSS