Wednesday Aug 13, 2008

Backup-Restore using pgAdmin on OpenSolaris

If you really like using pgAdmin to administer you Postgresql server instance, then this tip is important for you.

Solaris/OpenSolaris supports many versions of Postgres. Currently, OpenSolaris supports postgresql version 8.1, 8.2 and 8.3; which means you can install any of these versions from the repository and it will work just out of the box. pgAdmin, a postgresql database administration/monitoring tool (also available in the repository) is not tied to any specific version of the postgres server. So, You can install pgAdmin from the repository and simply connect to the postgres server that you are using.

Now, there is small glitch here. If you are trying to run programs like pg_backup, pg_restore from pgAdmin (Option Backup, Restore on database), then you need to be careful. pgAdmin expects these binaries to be present in the same directory as itself. i.e. /usr/bin. However, different postgresql server binary versions are installed in different locations, such as,


Postgres 8.1 : /usr/bin

Postgres 8.2 : /usr/postgres/8.2/bin

Postgres 8.3 : /usr/postgres/8.3/bin

Now, Lets say, you are using pgAdmin to restore a database of version 8.1, then you are fine. But, if you do not have 8.1 installed, pgAdmin will simply disable these options from the menu, as it would not be able to see the binaries at all. Also, If you have both 8.1  & 8.2 installed, but you are using 8.2 server then pgAdmin will use backup & restore binaries from the 8.1 bin directory, simply because they exist in the same directory as pgadmin. This will result in inconsistent behavior.

The best way to resolve all such issues is to create a symlink to /usr/bin/pgadmin3 from the bin directory of the server you are using. So, e.g., If you are using 8.2, then you could do following,


bash# ln -s /usr/bin/pgadmin3 /usr/postgres/8.2/bin/pgadmin3

and the same for 8.3 as well. Obvious that, this is not required if you are using 8.1 server.

then just include the 'bin' directory  of your server at the begining of your  PATH and you will always use  pgAdmin and the correct backup/restore binaries.

Saturday Jun 14, 2008

SMF import for IPS packages in opensolaris


Installing postgresql/mysql packages from IPS repository on OpenSolaris 2008.05 is very simple n easy and Jignesh has described the command line steps in his post in detail. I tried the same thing on a fresh install of OpenSolaris, but using the Package Manager GUI and It is as easy as it should get with GUI tools.

The idea behind this blog, is to simply clarify to users/developers that as soon as you install any PostgreSQL/MySQL version, the SMF registry is not going to be updated. This might look obvious to many of you, but I see on some reviews that people are expecting that the SMF service will be installed in the registry.

A subsequent reboot will automatically import necessary entries in the registry or it can be manually added before reboot if desired, as below,

# svccfg import /var/svc/manifest/application/database/postgresql.xml
(for Postgresql version 8.2)

This needs to be done only once and the registry is updated permanently.

Also, Note that registry entries are not cleaned up once you uninstall these packages. You can manually delete them as given below. There is no harm in keeping them in the registry, but they are not going to be useful for obvious reasons.

# svccfg delete svc:/application/database/postgresql

Saturday May 24, 2008

Day 2 @ PGCon 2008


The 2nd day of the PGCon conference also had a good list of hacker/community sessions lined up. The talks started at 10 am instead of the usual 9 am time, may be because, people getting late due to the EnterpriseDB party previous night was anticipated.

I was initially planning on attending PostGIS, but then changed my mind to attend "Deploying PostgreSQL in a Windows Enterprise" instead. Magnus covered how Postgres can be deployed with Active Directory Server connected network. I have been working on Solaris for a long time, so one of my intentions was to understand how different it is to deploy Postgres on windows platform and  possibly get an idea of how widely it is being used. I think my understanding is improved a bit after the talk and have realized that the problems are of similar nature in windoze too.

Later, I walked in "Security Enhanced PostgreSQL" session and it turned out to be a very cool project which I was not really aware of. This project provides fine grained mandatory access control even for the privileged user (such as 'postgres'). It depends heavily on the security context associated with certain tuple/cell. For PostgreSQL to be able to achieve such fine grained control, security enhanced operating system is necessary. KaiGai, the SE-PostgreSQL developer, demonstrated this on SELinux and it was really very exciting. I am now wondering if this can be implemented easily on Solaris 10 Trusted Extensions (Tx). He mentioned that for other operating systems to run SE-postgresql, there is a framework available and only certain modules need to be ported.

Post lunch, I attended "PL/Proxy, pgBouncer, pgbalancer" by Asko from Skype. I had a bit of idea about these tools and have a learnt a lot more about them from this talk. I think Skype contributing a bunch of tools is really appreciated. For a postgresql dbms user company to be able to solve their practical problems by creating tools that will help many many other users is really very creditable. pgProxy seems to be a really cool idea and the combination with pgBouncer seems to be really useful for customizing specific deployments which are exposed to heavy load. Kudos to the Skype team working in the PostgreSQL community!

For the next talk, "Performance of PostgreSQL", the speaker, I think at the last moment, decided to get a translator friend for his talk. I was disappointed by the  content of the talk. Lots of data was presented but it did not take long to realize that the tests were not performed taking all the aspects into consideration. That sort of took all the flesh out of the data presented. The translator did a good job though.

Zdenek then presented his findings and approaches on 'In-place upgrade' project. He demonstrated a working version of live upgrade from 8.1 to 8.2. He then presented various approaches to implement the same for other latest versions. Version 8.3 and later has some changes to the on-disk format which is complicating such conversion. The talk was  received very well. It was quite evident that the hacker community is really serious about this project and hence it will be interesting to see how the project shapes up in the near future.

Dan did a great job (again!) with his closing session. I think he was phenomenal in making this conference a huge success.

Most of us got together at the Royal Oak pub for the last time this year. I have never been to pubs for so many consecutive days in my life, but It was a lot of fun! Many of us are meeting up for a sightseeing tour tomorrow and then I will leave Ottawa on sunday to go to Niagara Falls and then back to India.

Friday May 23, 2008

Day 1 @ PGCon 2008


On the first day of the main conference here at PGcon, I was awake early with continued excitment of having won the Nintendo Wii and a slight hangover. I got ready early and after finishing some mails headed for breakfast. I was feeling good not only about the Wii but also because of the fun and the geeky mood here at the conference. The last 2 days had been fantastic with the tutorials and the start of the main conference today with a very interesting line up of talks was very exciting.

After the breakfast we arrived at the venue of the conference where registrations were still going on. The opening talk (keynote) by Bruce was good. The main focus for the next years for postgresql is clear. The project will work towards making effective use of multicore/multithreaded systems which are becoming a commodity now. It made total sense to me.

The first session I attended was "postgresql: from a Java Enterprise point of view". Given my long association with Java as a developer I thought this will be interesting to see how fellow java developers think of postgresql. The talk covered the J2EE aspects of entity beans, EJB/QL fairly in detail with changes in advanced versions. I was also hoping to hear how postgresql is perceived in J2EE application developer community, but that was not covered. It was more of PG user - Java developer talk than for the hackers.

I attended Logic and databases session with no expectations but simply thinking what would be the content of this one. I thought it was a very interesting talk for the users/hackers going back to the basics of relating logic to the query language and how a better understanding of this could improve the queries that one writes. Well, I learnt about some good pitfalls and that helped improve my understanding. Thanks Jeff.

then I attended Susanne's talk on "What PG could learn from MySQL". She put together the content based on her experience and with some help from her colleagues both in PG and MySQL commuity. Her analysis was based on various aspects like development model, packaging, User interface, user/customer focus and I agreed with most of the data that she presented, but not necessarily all the conclusions. One thing I liked about the pg hackers during the talk was that they were very receptive about what she had to present and I am sure that will certainly help postgres project.

"Problems with PostgreSQL with multi-core systems with Multi tera-byte data" by Jignesh Shah. To me, this was the best talk so far at the conference. He presented his study with a lot of data on the performance of postgres on multi-core high end systems. The graphs were complicated but he made some very good points about implementaion 0f postgres and also presented his thoughts on how this can be improved. It was a very detailed talk and I must say very well presented to the hacker community. I really felt excited about the performance of postgres if we resolve some of the problems with it today and I saw similar expression on everybody else' face after the talk. The points Jignesh made will surely help postgres to achieve their goal for the next few years, I believe. Boy! I love dtrace!!

I wanted to attend search.postgresql.org to see how good the new search system on postgres website is going to be and it looks really promising. I am sure it will be very helpful for all the hackers and more useful for the novice ones like me.

I was very excited about the last talk "Multi threaded query accelarator". The idea of multi threading at the application level giving high overall performance with pg backend was certainly exciting. Not only that, they have also deployed such solution in production made me walk into that room instead of going to the lightining talks which I are always more fun. The talk presented detailed design of their systems and some analysis on how multi-threaded application could improve the overall performance. I personally thought, their database design was not too complicated which allowed them to get performance even if they added some complexity at the application level. Also, I felt, multi threading at application level is going to give performance boost of limited range for OLTP and data-warehousing applications. This is in a way obvious and got confirmed from the presentation to me.

I could attend some of the lighting talks and happy that I didnt miss out on DBIx::cache and dtrace ofcourse!

EnterpriseDB hosted dinner & drinks at a local pub and I must thank them for the nice food/desert and drinks they arranged and yes, a free Tshirt. :-)

Socialising is always good !

About

user13377336

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Bookmarks
Blogroll

No bookmarks in folder