Here's another plug for FreshPorts. I've been playing with ideas and I've come up a few good improvements. But they will require a database with more features than mySQL. Specifically, I'm going to need stored procedures and functions.I actually installed PostgreSQL back in late July, but never did anything with it. This article will help you along the way. PostgreSQL caught my attention because it's been recommended by others. It also has the stored procedures and triggers. These facilities will form the heart and soul of the new database. NOTE: Since this article was written, the path for the PostgreSQL binaries has changed. When I wrote this article, the pathnames were /usr/local/pgsql/bin/ . I have since updated the article to refer to the new location /usr/local/bin/ . |
Installation - from ports
|
If you want to use php first, well, I've already installed that. I'm not sure what you'd do if you want PostgreSQL and php. Perhaps install mod_php later. Does anyone know? If so, add your comments.As always, I'm installing this from ports. If you haven't already installed your ports tree, you should. Because this is how easy it is to install a port:
There. Done.Actually, I'm sure there might have been more to it than that. Such as specifying php options. But I can't recall. You also need to initialize the database with the following command [note that the user pgsql is not used on all systems, on some systems it might be postgres ):
NOTE: Recent versions of FreeBSD will require this entry in /etc/rc.conf in order for PostgreSQL to start:
Now I'm ready to start the database server:
NOTE: in recent versions of PostgreSQL (e.g. 8.2), the command is:
That should be the name of the file (i.e. 010.pgsql.sh ) but if you can't find it, just hunt around in that directory for a simlarly named file. |
Allowing users to use psql
|
This section documents the steps required to allow a user to access a database. You may also want to read the instructions for adding a database user. In this section, the term user refers to a login.I like the way PostgreSQL works. It creates a special user for you, pgsql. This user does all the work. The database runs as this user, and all work (database creation, adding users, etc) is done as this user. NOTE: pgsql is not used on all systems, on some systems it might be postgres .The first step is to add myself as a user, so I don't have to do all my work as pgql. Here's how I added myself as a user. I typed the bits in bold. See also the NOTE below regarding the path.
Done. Now that I've added myself as a user who can create databases, I can use my normal login.NOTE: In more recent versions of PostgreSQL, the binary is /usr/local/bin/createuser . |
Adding a database
|
Now I dropped back to my usual login and created a database.
Done. |
Creating a user for this database
|
Now I dropped back to my usual login and created a database.
Now I'll create a user, tester, for this database.
To remove a user:
NOTE: In recent versions of PostgreSQL, CREATE USER has been deprecated by CREATE ROLE. |
Creating groups
|
You can also create groups and place the users in those groups. You can grant permissions collectively to the group instead of individually to the user.
Then you can grant SELECT permission on table thedata permissions to group testers :
|
Creating a table
|
I created a rather simple table for my testing.
Then I inserted data:
Then I read that data back out:
|
Getting php going
|
I create a simple php test in an existing website. For help on creating websites, look at Apache - virtual hosts.I added this to testpsql.php3 in my website. Note the amended while loop at the end of this section.As you can see, I had to manually break the loop. I have no idea why. I thought pg_fetch_array would return false at the end of the result set, as mentioned in the documentation. But it didn't. So far, it appears I'll have to use a for loop for that and not a while. Any ideas on why should be added as comments. pg_fetch_array was behaving like that? It seems to be standard behaviour.<head> <title>PostgreSQL test</title> <body> <?php $database=pg_connect("dbname=mydb user=test password=mypassword"); if ($database) { $result = pg_exec ($database, "select * from test"); if ($result) { echo pg_numrows($result) . " rows to fetch\n"; echo "<table>\n"; $i = 0; while ($myrow = pg_fetch_array ($result, $i)) { $i++; echo " <tr><td>" . $myrow["id"] . "</td><td>" . $myrow["name"] . "</td></tr>\n"; if ($i > 10) break; } echo "</table>\n"; } else { echo "read from test failed"; } pg_exec ($database, "end"); } else { echo "no connection"; } ?> </body></html> A search at http://google.com found this example, which I used to create this amended while loop: for ($i = 0; $i < $NumRows; $i++) { $myrow = pg_fetch_array ($result, $i); echo " <tr><td>" . $myrow["id"] . "</td><td>" . $myrow["name"] . "</td></tr>\n"; } |
What's next?
|
I would like a Windows GUI inteface to PostgreSQL. Any suggestions should be added to the comments. I found ZEOS, but couldn't get it to connect. I suspect someone wrong with my access rights, but I was looking at /usr/local/pgsql/lib/pg_hba.conf.I'm sure the next PostgreSQL article will have more information. |
backups
|
It's time I added backups to this article. This information is taken from the Admin documentation at /usr/local/share/doc/pgsql/admin/.A backup is done with this:
A restore is done with this:
Depending upon your path settings, you may have to specify the full path to these binaries. Under FreeBSD, this would be /usr/local/bin/pg_dump.For a backup script, please read the section on mySQL backups in the article I wrote for mySQL. Just substitute pg_dump for mysqldump. |
Various notes
|
Tonight I was upgrading, accidentally mind you, from 7.0.3 to 7.1.3. I did this without first deinstalling the old version. Bad idea. As a precaution, you should always dump your old databases before upgrading. I didn't. When I tried to run psql, I was getting these errors:
The mistake was that I was doing this as root. DOH! I had created all my databases as dan. So asking on IRC, I was told to do this:
That worked. I then dumped all my databases as shown in a previous section. Then I saved them all to CD.Then I did the right thing:
Then I had to do the initdb manually (and I'm not sure if this is usually done automatically):
Then I started the database server:
NOTE: in recent versions of PostgreSQL (e.g. 8.2), the command is:
Note that this script had been sitting around from my previous install. It may not be the same name on your system, but it will be in the same directory. |
Improving performance
|
If you find that a query isn't running fast enough, look at the situation and act accordingly. Here is an example I encountered when working on FreshSource. I was looking for all the children of a particular element: freshports=# select * from element where parent_id = 77340; id | name | parent_id | directory_file_flag | status -------+----------+-----------+---------------------+-------- 77341 | files | 77340 | D | A 77449 | Makefile | 77340 | F | A 77450 | distinfo | 77340 | F | A (3 rows)
As you can see, this query is accomplished by doing a sequential scan on the element table and it takes 0.6s. Let's refresh the statistics on this table, and then run the query again.
That gets us down to 0.2s, but we are still doing a sequential scan. Let's try an index.
OK, now that is impressive. We've gone from 600ms to 0.6ms. That's 1000 times faster overall. For more information, read the documentation regarding vacuum . |
Wednesday, January 21, 2015
PostgreSQL: Installation and configuration
Subscribe to:
Post Comments (Atom)
i am a successful business owner and father. I got one of these already programmed blank ATM cards that allows me withdraw a maximum of $5,000 daily for 30 days. I am so happy about these cards because I received mine last week and have already used it to get $20,000. Skylink technology is giving out these cards to support people in any kind of financial problem. I must be sincere to you, when i first saw the advert, I believed it to be illegal and a hoax but when I contacted this team, they confirmed to me that although it is illegal, nobody gets caught while using these cards because they have been programmed to disable every communication once inserted into any Automated Teller Machine(ATM). If interested get through to them on mail: skylinktechnes@yahoo.com or whatsapp/telegram: +1(213)785-1553
ReplyDelete