What type of PostgreSQL setup is used ?
Each account has its own postgresql server, which he manages.
You can create any number of postgresql users, databases.
You also control access methods to the postgresql server.
Can I use both MySQL and PostGreSQL in the same account ?
Yes, you can use both at the same time. On rootr.net, mysql and
postgresql system don't interfere with each other at all.
You can even use them both inside one program or script.
How many database can I create under PostGreSQL ?
There is no arbitrary limit on the number of datbases you can create.
You have full administrative access to your own postgresql server.
Major programming languages and PostGreSQL on rootr.net:
rootr.net has already set up to use postgresql interface libraries.
Other programming languages would use the same settings.
Ruby: Ruby postgres and ruby/DBI with DBD/pg.
Perl: use DBI; (itself using DBD::Pg) modules. man DBI.
PHP: build-in standard postgres extensions for PHP4 and above.
Python: py-psycopg, and py-psycopg-zope.
C and C++: Native postgresql libraries.
Shell commands:
The psql command for SQL client interface.
The createdb, dropdb
commands for database creation.
The createuser, dropuser
commands to add and delete users.
The initdb, pg_ctl
and postmaster commands for administration.
All have associated man pages.
SSH tunnels can be created freely, for remote access security.
How to setup PostGreSQL in my Account ?
From the shell, type the command: install_pg
That's it. Alternatively, shoot an email to support.
I know already about postgresql, what does install_pg do ?
- Create database directory, default to ~/pg
- Install database system
- Edit the postgresql.conf for the port.
- Create a server start/stop scripts in ~/pg/start
- Edit crontab to start server on reboots.
- Starts the server.
- Create a default user database.
- Send an email notice to the calling user,
and saves a text copy in ~/pg/email.txt.
So basically you're up and running, ready to go.
It can take one argument: the database directory instead of "pg".
One PostGreSQL user is created, with the same name as your main account,
and no password. see below to change the password.
This install script does run a number of checks and avoid overwriting
existing data and files. In which case it will fail and stop
with an error message, and exit status 1. If all goes well,
exits status 0.
How to create PostGreSQL users ?
When PostGres is installed in your account, there is a default
user with the same name as the account (login) name.
You can create additional users with the createuser command:
createuser <new-user-name> . see man createuser
For details on options to create user from the shell, in particular the
-p and -d options.
From SQL: CREATE USER <new-user-name>. inside
psql, see \h CREATE USER for details.
This same SQL command can be used within applications and programs.
Changing PostGreSQL users password
This SQL command change a specific user's password:
ALTER USER [user name] WITH PASSWORD '[new password]'
If you have not created users yet, put your main login name for [user name].
I want to install a PostGreSQL manually, on my own
No problem. the script above is just convenience.
Here are quick-steps to install your own postgresql server on your own:
- issue the following command from the shell:
initdb -D pg
replace "pg" with a directory of your choice.
- Then edit the file pg/postgresql.conf
and change the port number to the one (NN) support@rootr.net allocated you (shot
an email to support if you don't have one already):
port = NN
- Also add this in your .bash_profile (or .profile):
export PGPORT=NN.
If you use csh or tcsh: setenv PGPORT NN.
- To start the postgresql server:
pg_ctl -D pg -l pg/log start
- Then create a datbase:
createdb test
replace "test" with a database name of your choice.
- The postgresql system is now ready, and can be
accessed with:
psql test
- Remember to set an @reboot cron job for your postgresql server:
crontab -e , then add the line:
@reboot /usr/local/bin/pg_ctl -D $HOME/pg -l pg/log start >/dev/null 2>&1
This will make sure your postgresql server starts when the physical server is restarted.
The install_pg script does actually quite more than that, but this list is
enougth to get it running.
Tip: you can change the default crontab editor by setting the VISUAL environment variable,
for example:
export VISUAL=/usr/local/bin/uemacs
Documentation from the command-line, type:
links /usr/local/share/doc/postgresql/html/index.html
After installing PostGreSQL with install_pg automatically, can I edit it ?
Yes, install_pg is non-interfering, and automate only the installation process.
once it is installed, you can change anything in postgresql as if it was
installed manually.
Removing a PostGreSQL server entirely
To uninstall all this, do the following in the home directory:
(replace "pg" with wherever you installed it)
- Stop the server, for example with:
pg/stop
or with pg_ctl -D pg stop
or with some tough commands like: kill -9 `cat pg/postmaster.pid` (backstick quotes).
- Remove the database directory with:
rm -r pg
- Edit crontab and remove the line:
@reboot pg/start, if there's any.
That is it.
