4

I want to learn PostgreSQL and How to make the learning process efficient, but really don't know how long it would take even after googling my question.

I used MySQL before and knew relational database. However, I would like to use PostgreSQL the implement my web back end database management system. I want sufficient knowledge to do it, design not ugly structure, write not ugly code, like I used MySQL for another web app project before.

I am not asking the specific time, but at least I want a sketch in my mind. How much effort I need to invest? What kind of problem I would have? Any lessons you learned can be shared?

Thank you very much!

9

As developer, not a full-time DBA, I use Postgres for some of my work. But it is not my focus. I found Postgres very confusing and frustrating when getting started. After a long career using 4D, I was an pro in relational database design and normalization, but a newbie with SQL and "black-box" database engines. Below is a list of the resources that helped me.

I look at Postgres not as a product to be mastered such as a new IDE or email client app. I see it more as a computer system unto itself, to be explored bit by bit. Some areas I may never touch (PostGIS, geo data), while others are vital to me (UUID data type, XML data type, date-time data types).

➥ So I see "learning Postgres" not as a goal to be accomplished but as an on-going process.

The more I learn about Postgres, the more impressed and awed I become. It took some investment of time and effort, but now I ♥ 🐘 (the Blue Elephant).

Tips

Smart Defaults

When in doubt, go with Postgres’ defaults.

UTF-8

One exception to the 'smart defaults' tip: Almost always best to specify UTF-8 as the character encoding. Do so when installing Postgres, as this defines the default when you later create databases. When you do create databases, verify that UTF-8 is the encoding is in use.

Security

Postgres is designed for security as a priority. By default you can only connect from a process/app running locally on the same machine. You must change the security configuration to connect from across a local network or internet.

Installers

To get started you must install Postgres. The main 3 ways to do that:

On some hosted servers such as Heroku or Amazon, the host may offer to spin up Postgres on your behalf. So no installation required in that case.

Regarding uninstalling:

Client-Server

Postgres is a black-box, hiding your data inside. To access, you must use a utility tool to make a connection to the Postgres daemon process. For getting started, use either of these tools usually included with your Postgres installation.

  • pgAdmin for GUI app access
  • psql for command-line access

Virtual Machines

When playing around or experimenting with Postgres, I suggest using a virtual machine so you can later throw it away. Postgres is a pretty heavy installation, creating a new Unix-level user. Entirely removing Postgres can be a bit tricky.

Local

I use Parallels 9, 10, and 11 for virtual machines running Mac OS X Mountain Lion or El Capitan to install various versions of Postgres 9.*. On a Mac, besides Parallels, you might be able to use VMware Fusion or Oracle VirtualBox.

Cloud

Another route is a cloud server. Renting by the hour can be quite inexpensive, perhaps in the range of a penny (USD $0.01) or nickel or dime an hour. You should be able to delete your virtual machine when done or when you want to start from scratch with a fresh one. Examples include DigitalOcean, Heroku, and Amazon.

In-Person Resources

PUGs

Find a PUG. Not a dog, a Postgres User Group. Search MeetUp.com and search this list.

Attend every meeting. Let discussions wash over you even when you cannot understand it all. Postgres has many features, add-ons, related products. Postgres has much depth. So don't expect to master Postgres, just keep looking for ways to use it.

If you cannot find one near you, start one.

Experts

Learn the names of some of the Postgres experts who are generous about sharing their knowledge. Pay special attention when they speak. For example, Craig Ringer on this page. Other examples, Josh Berkus, Bruce Momjian, David E. Wheeler, Selena Deckelmann.

Events

pgDay

You can find one-day "pgDay" events as well as multi-day Postgres conferences all over the world. For example, France, Prague, India, Finland, and Singapore. See this list.

Multi-Day Conferences

In North America the multi-day conferences include:

Other Events

You can often find a Postgres booth at other geek events as well.

For example, visit me while volunteering at the SeaPUG booth next April at LinuxFest Northwest 2016 in Bellingham WA, USA (outside Seattle). Postgres consulting companies have had booths in the past.

Online Resources

Postgres Weekly

Read Postgres Weekly, a collection of select Postgres-related articles and news. Curated by an expert, Craig Kerstiens. Published weekly, thus the name.

Mailing Lists

Subscribe to the Postgres mailing lists.

I find it enlightening to skim through the weekly PostgreSQL Weekly News email delineating that week's work including a list of patches submitted. Posted on the Announce list. All the nitty-gritty details are beyond my comprehension, but nevertheless I become more and more familiar. I learn a bit about the many different features inh Postgres. And I learn how seriously the team takes their work, with an emphasis on solid quality.

Planet Postgres, blog portal

Planet Postgres is a consolidator of Postgres-related blogs.

6

It shouldn't take long to get up to speed to a reasonable degree if you've used another RDBMS. Read some guidance on PostgreSQL for MySQL users to help you adapt to sequences vs auto_increment, ANSI-standard quoting (though you should be using that in MySQL already), the stricter data type checking, how authentication and roles work, psql's backslash commands instead of MySQL's SHOW TABLES, etc.

There are plenty of resources out there. Google has many, many more.

If you run MySQL in ANSI STRICT mode, you're going to have less trouble moving to PostgreSQL.

More learning is required if you're setting up replication and failover, and for managing backups etc, as they're significantly more different than the SQL-level interface is.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.