Category Archives: PostgreSQL

MySQL and PostgreSQL rosetta stone

This is a short table of useful and common MySQL & PostgreSQL commands put up against each other.

MySQL

PostgreSQL

Command line client mysql psql
Connect to database use mysql; \connect postgresql;
List databases show databases; \l
List tables show tables; \dt
Describe table describe table; \d table;
Show server version select version(); select version();
Show the current time select now(); select now();
Authentication GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’localhost’; (http://dev.mysql.com/doc/refman/5.5/en/adding-users.html) Edit pg_hba.conf (http://wiki.postgresql.org/wiki/Client_Authentication)
Check user privileges SHOW GRANTS FOR ‘root’@’localhost’; \du (List all users and their permission levels)
Backup mysqldump [options] db_name [tbl_name …] pg_dump [option…] [dbname]

Note for future: This reference list will grow.

References

http://dev.mysql.com/doc/index.html
http://www.postgresql.org/docs/manuals/

Notice

Table shows correctly in Mozilla Firefox but it does not in Google Chrome or any other Webkit browser for that matter. Go figure.

// CrashMAG

Resetting the root/postgres password for PostgreSQL

The following is required to reset the root/postgres user password for PostgreSQL. The distribution used in my example is CentOS 5.5 and PostgreSQL 8.4.

Note: By default there’s no password for the postgres user.

In step 2 and 5 you will most likely not be using “ident” but rather “password” or “md5”.

1. Shut down PostgreSQL

# service postgresql stop

2. Reset the authentication mechanism (assuming defaults are already being used)

Edit the /usr/lib/pgsql/data/pg_hba.conf file

# nano /usr/lib/pgsql/data/pg_hba.conf

Navigate down to the line that says

local all all ident

Edit it to

local all postgres trust

And now save the file.

3. Start PostgreSQL

# service postgresql start

4. Log in and change the password

# su - postgres
$ psql -d template1 -U postgres
alter user postgres with password 'new_password';

Or alternatively do it all in one go with the following command

> psql -U postgres template1 -c "alter user postgres with password 'new_password';"

5. Reverse the actions you did in step 2

Edit the /usr/lib/pgsql/data/pg_hba.conf file

# nano /usr/lib/pgsql/data/pg_hba.conf

Navigate down to the line that says

local all all trust

Edit it to

local all postgres ident

And now save the file.

6. Start PostgreSQL

# service postgresql start

Success!

// CrashMAG

Changing the default PostgreSQL data folder (PGDATA)

Installing the PostgreSQL server on RHEL, CentOS, Scientific Linux or Fedora installs the PostgreSQL databases and configuration files in “/var/lib/pgsql/data”.

This may or may not be desirable. Let’s assume for a moment you have a separately crafted partition for PostgreSQL to use, let’s say a RAID10 volume. You’d want to change this.

Change the defaults

Use your favorite text editor, in my case nano to create the following file (must be the same as the name of the service)

# nano /etc/sysconfig/pgsql/postgresql

Add the following

PGDATA=/postgresql/data

Optionally you can also add the following to change the default port (example is the default port)

PGPORT=5432

Adjusting SELinux to permit the new data folder (pgdata) location

Should the following command output “Permissive” or “Disabled” then you may skip the details for SELinux.

# getenforce

Run the semanage command to add a context mapping for /opt/postgresql and any other directories/files within it.

# semanage fcontext -a -t postgresql_db_t "/postgresql/data(/.*)?"

Now use the restorecon command to apply this context mapping to the running system

# restorecon -Rv /postgresql/data

Starting PostgreSQL

# chkconfig --levels 345 postgresql on
# service postgresql initdb
# service postgresql start

You’re all set to go! Keep in mind that PostgreSQL listens to ‘localhost’ by default. To change this you need to alter the “listen_address” parameter in “/var/lib/pgsql/data/postgresql.conf” (change will require restart).

// CrashMAG