|
From: David G. <da...@co...> - 2012-01-13 20:42:25
|
> But, I'm thinking/hoping it is more my thought processes than it is a > matter of postgresql being *that* much more complicated. > > I am about as far from an 'expert' at anything sql as anyone could be, > but I have found managing my mysql to be very painless and easy, and I > don't really have the time to become a postgres guru just to run my > postfixadmin database. > > So, if I can get some help with doing the following things the postgres > way (these are about the only things I ever do in mysql, up to now that > is), then maybe I can give switching another look... > > Following is my documented procedure for installing and/or updating my > postfixadmin in mySQL - what I need help with is how to do the > equivalent in postgresql. I appreciate any tips you can provide... > > ************************************************* > > Updating PostfixAdmin after emerging the new version and installing it > with webapp-config: > > 1. dump the current/production DB to an sql file: > > From the root commandline (NOT the mysql command line): > # mysqldump -u root -p -v pfa_currentver > > /home/user/pfa_currentver_preupdate.sql > > pgsql equivalent: > As the system user postgres : pg_dump -D postfix > postgres.sql (from memory). > > 2. create the admin user (if already exists, skip this step): > > mysql> CREATE USER 'pfa_admin'@'localhost' IDENTIFIED BY 'password'; > > pgsql equivalent (maybe?): > pgsql> CREATE USER pfa_admin WITH NOSUPERUSER NOCREATEDB NOCREATEROLE > INHERIT PASSWORD 'password'; > Postgres has a different permissions structure. Create user postgres with password 'fish' (I think) > 3. create the new DB: > > # mysql -u root -p password > > mysql> CREATE DATABASE pfa_newver; > > pgsql equivalent (maybe?): > pgsql> CREATE DATABASE pfa_newver OWNER pfa_admin; ? > Yep. > 4. grant required access to the pfa_newver DB to the pfa_admin user: > > mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, > INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE > ROUTINE, ALTER ROUTINE ON `pfa_newver`.* TO 'pfa_admin'@'localhost'; > > pgsql equivalent? > No need. Ownership fixes that. > > > > 6. create a read-only user (if already exists, skip the CREATE command) > and grant only select privileges for querying the database: > > mysql> CREATE USER 'pfa'@'localhost' IDENTIFIED BY 'password'; > > pgsql equivalent (maybe?): > CREATE USER pfa WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT > PASSWORD 'password'; > Not sure. Will have to research / read docs. > > 7. import the dump from the production DB into the new DB: > > # mysql -u root -p password pfa_newver < > /home/user/pfa_currentver_preupdate.sql > > pgsql equivalent? > psql postfix < postfix.dump You might need to specify user etc when connecting. > Somewhere on my website (codepoets.co.UK) there should be a tutorial covering Installation with pgsql. David. |
