9 ms·

Having recently been bitten by a 20 hour schema change to a very large table in MySQL I have been looking at Postgresql more seriously. My biggest issue with i

by boyter 15y ago

Having recently been bitten by a 20 hour schema change to a very large table in MySQL I have been looking at Postgresql more seriously. My biggest issue with it having used it for other projects is getting the dammed thing setup and working to begin with. I could never find a decent tutorial (or rather one that fits my mindset) of how to do the following, 1. Install 2. Setup users, including how to set it up on a local development machine with 'root' user who can do anything (saves time in dev) 3. Import/Export SQL/Backup files I managed to do all 3. Once. I'm sure its out there, and I would switch in a heartbeat were these steps much easier to work out. As it is I can just copy the table, run the alter in the background and rename the tables the next day. Slower, but doesn't cost me any time and works.

jeltz 15y ago
What is your platform? If it is Debian then it is as simple as this to install PostgreSQL and set up a PostgreSQL root user with the same username as your UNIX username (this to avoid having to modify pg_hba and and also not having to specify user when connecting to PostgreSQL). sudo apt-get install postgresql sudo -u postgres createuser -s `whoami` After this you an just create a database and connect to it with: createdb my_db psql my_db
- boyter 15y ago
  Usually Ubuntu, so I will have a look at this. However when running an application say in PHP its going to be running under the Apache user. How do I go about configuring that?
  - jeltz 15y ago
    I see, I usually do ruby development so the webserver is then usually running as my user on my development machine (obviously that is not the case in production). In your case, then I would just change pg_hba to allow all local connections as shown by moe above. (I would not set any password though since that is normally not useful or necessary for local development).
- KC8ZKF 14y ago
  sudo -u postgres createuser -s `whoami` Won't this create another user named "postgres"? EDIT: No it won't: Ophelia ~ $ sudo -u postgres whoami postgres Ophelia ~ $ sudo -u postgres echo `whoami` rich That seems straight out of The Unix Hater's Handbook.
  - caf 14y ago
    The idea that commandline expansions like $VAR, `backtick` and wild*cards are done by the shell, before the program is invoked, is not particularly esoteric is it?
    - KC8ZKF 14y ago
      No. But, I had to think about it.
moe 15y ago
The bootstrap is not really hard when you've done it once, just jot it down somewhere. In my experience most people stumble because the network security in postgres is pretty tight by default. This is easily fixed and needs to be done only once. First: Find your pg_hba.conf. It's in the database-directory, that's often linked to /etc/postgresql. # Backup the original $ cp pg_hba.conf pg_hba.conf_orig # Now replace it with our desired security settings $ cat >pg_hba.conf <<EOF # Require password auth from remote hosts # localhost and local socket are trusted host all all 0.0.0.0/0 md5 local all all trust host all all 127.0.0.1/32 trust EOF # restart $ /etc/init.d/postgresql restart From now on you can connect as any user from localhost without a password. Thus, we can now just go about our business. # connect as user postgres (the super-user) $ psql -U postgres postgres=# create database dummy; postgres=# create user bob with password 'pony'; postgres=# grant all on database dummy to bob; postgres=# ^D # Now you have a database that user bob can use. # From remote he will have to use the password 'pony'. # From localhost no password needed because of our pg_hba settings above. $ psql -U bob dummy bob=> create table ...
- jeltz 15y ago
  Note: You need not restart PostgreSQL after changing pg_hba.conf. This is enough: /etc/init.d/postgresql reload
- boyter 15y ago
  That looks too be exactly what I have been missing. I have yet to see details like that not spread out over 5 pages. Thank you!
  - moe 15y ago
    Yes, the docs could use indeed a few lighter tutorials for people starting out. However, once you're over that initial learning hump and start looking for more specific things you'll quickly notice why the postgres manual is often cited as being one of the best documentations ever written (inside and outside the OSS world). It's really that good - once you're familiar with a few basics.
deleted 15y ago
[deleted]
sandGorgon 14y ago
Actually a bigger question is how are people migrating from their Mysql databases to postgresql. I'm quite surprised on the lack of a standard, production grade, defacto mysql2pg tool (and sql server as well).
caf 14y ago
pgdump to export as SQL, just pipe into psql to import.
- thristian 14y ago
  There are some caveats about piping SQL into psql: http://petereisentraut.blogspot.com.au/2010/03/running-sql-scripts-with-psql.html http://petereisentraut.blogspot.com.au/2010/03/running-sql-s...
wiredfool 14y ago
As for backup, pg_dump dbname > db.dump will do the dump, psql -f db.dump dbname will do the restore. There are, of course, far more complicated ways of doing it, including doing it in binary format, or data / schema only, or individual tables, but those are all a flag or two away.
lobo_tuerto 14y ago
Ok here is a guide I wrote for installing & setting up Postgres in Ubuntu. It's in Spanish but you will find your way around: http://lobotuerto.com/blog/2009/07/20/como-instalar-postgresql-en-ubuntu-9-04-jaunty-jackalope/ http://lobotuerto.com/blog/2009/07/20/como-instalar-postgres... It covers instalation, setting up the postgres user (the _root_ you want), changing the authentication schema so you can use it for Rails dev (or something else), and finally how to install the Postgres gem for your Ruby.

by boyter 15y ago

jeltz 15y ago

What is your platform? If it is Debian then it is as simple as this to install PostgreSQL and set up a PostgreSQL root user with the same username as your UNIX username (this to avoid having to modify pg_hba and and also not having to specify user when connecting to PostgreSQL). sudo apt-get install postgresql sudo -u postgres createuser -s `whoami` After this you an just create a database and connect to it with: createdb my_db psql my_db

boyter 15y ago
Usually Ubuntu, so I will have a look at this. However when running an application say in PHP its going to be running under the Apache user. How do I go about configuring that?
- jeltz 15y ago
  I see, I usually do ruby development so the webserver is then usually running as my user on my development machine (obviously that is not the case in production). In your case, then I would just change pg_hba to allow all local connections as shown by moe above. (I would not set any password though since that is normally not useful or necessary for local development).
KC8ZKF 14y ago
sudo -u postgres createuser -s `whoami` Won't this create another user named "postgres"? EDIT: No it won't: Ophelia ~ $ sudo -u postgres whoami postgres Ophelia ~ $ sudo -u postgres echo `whoami` rich That seems straight out of The Unix Hater's Handbook.
- caf 14y ago
  The idea that commandline expansions like $VAR, `backtick` and wild*cards are done by the shell, before the program is invoked, is not particularly esoteric is it?
  - KC8ZKF 14y ago
    No. But, I had to think about it.

moe 15y ago

The bootstrap is not really hard when you've done it once, just jot it down somewhere. In my experience most people stumble because the network security in postgres is pretty tight by default. This is easily fixed and needs to be done only once. First: Find your pg_hba.conf. It's in the database-directory, that's often linked to /etc/postgresql. # Backup the original $ cp pg_hba.conf pg_hba.conf_orig # Now replace it with our desired security settings $ cat >pg_hba.conf <<EOF # Require password auth from remote hosts # localhost and local socket are trusted host all all 0.0.0.0/0 md5 local all all trust host all all 127.0.0.1/32 trust EOF # restart $ /etc/init.d/postgresql restart From now on you can connect as any user from localhost without a password. Thus, we can now just go about our business. # connect as user postgres (the super-user) $ psql -U postgres postgres=# create database dummy; postgres=# create user bob with password 'pony'; postgres=# grant all on database dummy to bob; postgres=# ^D # Now you have a database that user bob can use. # From remote he will have to use the password 'pony'. # From localhost no password needed because of our pg_hba settings above. $ psql -U bob dummy bob=> create table ...

jeltz 15y ago
Note: You need not restart PostgreSQL after changing pg_hba.conf. This is enough: /etc/init.d/postgresql reload
boyter 15y ago
That looks too be exactly what I have been missing. I have yet to see details like that not spread out over 5 pages. Thank you!
- moe 15y ago
  Yes, the docs could use indeed a few lighter tutorials for people starting out. However, once you're over that initial learning hump and start looking for more specific things you'll quickly notice why the postgres manual is often cited as being one of the best documentations ever written (inside and outside the OSS world). It's really that good - once you're familiar with a few basics.

deleted 15y ago

[deleted]

sandGorgon 14y ago

Actually a bigger question is how are people migrating from their Mysql databases to postgresql. I'm quite surprised on the lack of a standard, production grade, defacto mysql2pg tool (and sql server as well).

caf 14y ago

pgdump to export as SQL, just pipe into psql to import.

thristian 14y ago
There are some caveats about piping SQL into psql: http://petereisentraut.blogspot.com.au/2010/03/running-sql-scripts-with-psql.html http://petereisentraut.blogspot.com.au/2010/03/running-sql-s...

wiredfool 14y ago

As for backup, pg_dump dbname > db.dump will do the dump, psql -f db.dump dbname will do the restore. There are, of course, far more complicated ways of doing it, including doing it in binary format, or data / schema only, or individual tables, but those are all a flag or two away.

lobo_tuerto 14y ago

Ok here is a guide I wrote for installing & setting up Postgres in Ubuntu. It's in Spanish but you will find your way around: http://lobotuerto.com/blog/2009/07/20/como-instalar-postgresql-en-ubuntu-9-04-jaunty-jackalope/ http://lobotuerto.com/blog/2009/07/20/como-instalar-postgres... It covers instalation, setting up the postgres user (the _root_ you want), changing the authentication schema so you can use it for Rails dev (or something else), and finally how to install the Postgres gem for your Ruby.