Archive for the ‘PostgreSQL Developer’ tag
Fedora PostgreSQL Install
Somebody asked how to put PostgreSQL on my Fedora image with Oracle Database 11g and MySQL. It’s fairly simple. You can check for the current download at yum.postgresql.org and then download it like this as the root user:
yum localinstall http://yum.postgresql.org/9.3/fedora/fedora-20-x86_64/pgdg-fedora93-9.3-1.noarch.rpm |
You should see the following output when the download is successful, don’t forget to type y to complete the download:
Loaded plugins: langpacks, refresh-packagekit pgdg-fedora93-9.3-1.noarch.rpm | 5.1 kB 00:00 Examining /var/tmp/yum-root-2EPf_J/pgdg-fedora93-9.3-1.noarch.rpm: pgdg-fedora93-9.3-1.noarch Marking /var/tmp/yum-root-2EPf_J/pgdg-fedora93-9.3-1.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package pgdg-fedora93.noarch 0:9.3-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: pgdg-fedora93 noarch 9.3-1 /pgdg-fedora93-9.3-1.noarch 2.1 k Transaction Summary ================================================================================ Install 1 Package Total size: 2.1 k Installed size: 2.1 k Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : pgdg-fedora93-9.3-1.noarch 1/1 Verifying : pgdg-fedora93-9.3-1.noarch 1/1 Installed: pgdg-fedora93.noarch 0:9.3-1 Complete! |
After downloading the packages, you install with the following command:
yum install postgresql93-server |
You should see the following output when the installation is successful, don’t forget to type y to complete the installation:
Loaded plugins: langpacks, refresh-packagekit pgdg93 | 3.6 kB 00:00 (1/2): pgdg93/20/x86_64/group_gz | 332 B 00:00 (2/2): pgdg93/20/x86_64/primary_db | 84 kB 00:00 Resolving Dependencies --> Running transaction check ---> Package postgresql93-server.x86_64 0:9.3.5-1PGDG.f20 will be installed --> Processing Dependency: postgresql93-libs(x86-64) = 9.3.5-1PGDG.f20 for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64 --> Processing Dependency: postgresql93(x86-64) = 9.3.5-1PGDG.f20 for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64 --> Processing Dependency: postgresql93 = 9.3.5-1PGDG.f20 for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64 --> Processing Dependency: libpq.so.5()(64bit) for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64 --> Running transaction check ---> Package postgresql93.x86_64 0:9.3.5-1PGDG.f20 will be installed ---> Package postgresql93-libs.x86_64 0:9.3.5-1PGDG.f20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: postgresql93-server x86_64 9.3.5-1PGDG.f20 pgdg93 3.6 M Installing for dependencies: postgresql93 x86_64 9.3.5-1PGDG.f20 pgdg93 1.0 M postgresql93-libs x86_64 9.3.5-1PGDG.f20 pgdg93 203 k Transaction Summary ================================================================================ Install 1 Package (+2 Dependent packages) Total download size: 4.8 M Installed size: 22 M Is this ok [y/d/N]: y Downloading packages: (1/3): postgresql93-libs-9.3.5-1PGDG.f20.x86_64.rpm | 203 kB 00:00 (2/3): postgresql93-9.3.5-1PGDG.f20.x86_64.rpm | 1.0 MB 00:01 (3/3): postgresql93-server-9.3.5-1PGDG.f20.x86_64.rpm | 3.6 MB 00:02 -------------------------------------------------------------------------------- Total 1.6 MB/s | 4.8 MB 00:02 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : postgresql93-libs-9.3.5-1PGDG.f20.x86_64 1/3 Installing : postgresql93-9.3.5-1PGDG.f20.x86_64 2/3 Installing : postgresql93-server-9.3.5-1PGDG.f20.x86_64 3/3 Verifying : postgresql93-server-9.3.5-1PGDG.f20.x86_64 1/3 Verifying : postgresql93-9.3.5-1PGDG.f20.x86_64 2/3 Verifying : postgresql93-libs-9.3.5-1PGDG.f20.x86_64 3/3 Installed: postgresql93-server.x86_64 0:9.3.5-1PGDG.f20 Dependency Installed: postgresql93.x86_64 0:9.3.5-1PGDG.f20 postgresql93-libs.x86_64 0:9.3.5-1PGDG.f20 Complete! |
You can confirm the installation with the following command:
rpm -qa | grep postgres |
It returns:
postgresql93-9.3.5-1PGDG.f20.x86_64 postgresql93-server-9.3.5-1PGDG.f20.x86_64 postgresql93-libs-9.3.5-1PGDG.f20.x86_64 |
You’ve now installed PostgreSQL but did you really? If you’re asking that questions you have other questions. Let me try to answer them quickly, here:
You have installed PostgreSQL and created a postgres user. postgres is the owner of the PostgreSQL database. You can connect to the database as the postgres user without credentials because that’s where you administer the database. However, you can’t connect using ssh as the postgres user. You must use sudo to assume the root user’s privileges and then use the su command to become the postgres user.
If you just completed the installation, you are the root user. You can verify that with a call to the whoami utility:
whoami |
It should return:
root |
You connect as the postgres user with the su utility like this:
su - postgres |
If you rerun the whoami command now, you should see:
postgres |
You can start the PostgreSQL command-line utility (psql), like this:
psql |
At the postgres (or psql) prompt, you can interactively confirm the setup of a database installation:
postgres=# SELECT setting as "Data Location" postgres-# FROM pg_settings postgres-# WHERE name = 'data_directory'; |
It should return the following:
Data Location ------------------------- /var/lib/pgsql/9.3/data (1 row) |
At this point, you should refer to this other blog post that shows you how to setup a new Database or Schema in PostgreSQL. You can find basic Postgres help files in this other blog post. As always, I hope this helps those timid about adding new software.
PostgreSQL New Database
How time flies, last March I explained how to install and configure PostgreSQL on Windows. It was my intent to start posting more content on PostgreSQL but I was distracted by another writing commitment on Oracle Database 12c PL/SQL Advanced Programming Techniques, which should be available in November. It tempted me away from PostgreSQL because I got to write about how to use Java inside Oracle Database 12c, which was fun. Having completed that, I’m back on task. Here’s the second entry on PostgreSQL. It shows you howto create your own database, database administrator role, user, and how to connect with psql CLI (Command Line Interface) as the new user.
- Create a user-defined
video_dbtablespace for your database. This requires that you know where the physical files where created when you installed PostgreSQL. You can discover the directory with the following query:
SELECT setting AS "Data Location" FROM pg_settings WHERE name = 'data_directory'; |
Data Location -------------------------------------- C:/Program Files/PostgreSQL/9.3/data (1 row) |
You create the video_db tablespace with the following syntax:
CREATE TABLESPACE video_db OWNER postgres LOCATION 'C:\Program Files\PostgreSQL\9.3\data'; |
You can check the presence of the video_db tablespace after creating it with the following query:
SELECT * FROM pg_tablespace; |
It should print:
spcname | spcowner | spcacl | spcoptions ------------+----------+--------+------------ pg_default | 10 | | pg_global | 10 | | video_db | 10 | | (3 rows) |
It’s important to note for those new to PostgreSQL that the pg_global and pg_default tablespaces are creating when initializing the database. The pg_global holds shared tables and the pg_default holds everything else.
- Create a database that uses your user-defined
video_dbtablespace with the following two commands:
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; COMMENT ON DATABASE videodb IS 'VideoDB'; |
- Create a database role, grant the super user privileges to the role, and create a user with the role. You can do that with the following three commands:
CREATE ROLE dba WITH SUPERUSER; GRANT ALL PRIVILEGES ON DATABASE videodb TO dba; CREATE USER video WITH ROLE dba PASSWORD 'video'; |
- Connect to the new
videodbdatabase with thepsqlCLI as thevideouser. You can do that with the following OS command:
psql -d videodb -U video |
- Once connected as the new
videouser, you can use a system information function to determine the current database:
SELECT current_database(); |
It should display:
current_database ------------------ videodb (1 row) |
There are many privilege options, and you should choose wisely which ones you use. As always, I hope this answers questions for other users.
Subscribe via RSS