Post PostgreSQL Installation
After installation of PostgreSQL start PostgreSQL by executing the command to see if the installation worked.
/etc/init.d/postgresql start
In the file ‘/var/lib/pgsql/data/pg_hba.conf’, replace all entries of “ident sameuser” with “trust”.
In the file ‘/var/lib/pgsql/data/postgresql.conf’, change the line “max_connections=100″ to max_connections=500″.
Restart PostgreSQL.
/etc/init.d/postgresql stop
/etc/init.d/postgresql start
How to add a user to PostgreSQL database
Following are the steps to add a user ‘testuser ‘ to the PostgreSQL database ‘testdb’ and grant access to that database;
First you need to make sure that the user is already existing in the server.
# grep testuser /etc/passwd
Else you need to create a user by type the following commands:
# adduser testuser
# passwd testuser
su - postgres
/usr/local/pgsql/bin/psql template1
/usr/local/pgsql/bin/psql -d template1 -U postgres (This command will result into the PostgreSQL command prompt)
Welcome to psql , the PostgreSQL interactive terminal.
template1=#
Now you need to add the user ‘testuser’ and give access to the database named ‘testdb’.
Follow the steps given below:
template1=# CREATE USER tom WITH PASSWORD ‘testPassword’;
template1=# CREATE DATABASE testdb;
template1=# GRANT ALL PRIVILEGES ON DATABASE testdb to testuser;
then quit from the template1 prompt:
Now you need to login as user ‘testuser’;
@ ~]$ su - testuser
Password:
[testuser@ ~]$ /usr/local/pgsql/bin/psql -d testdb -U testuser
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
testdb=>
Timeout error occurred when trying to start MySQL Daemon.
If you are getting the following error while trying to start MySQL service;
[root@ ~]# /etc/init.d/mysqld restart
Stopping MySQL: [FAILED]
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]
And if you are getting the following error in the MySQL log;
[root@ ~]# tail -f /var/log/mysqld.log
080503 14:30:28 [ERROR] Can’t start server: can’t create PID file: No space left on device
Number of processes running now: 0
080503 14:30:28 mysqld restarted
080503 14:30:28 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
The issue might be due to the lack of space in the ‘/var’ partition. So you need to reduce the disk space, which should fix the issue.
[root@ ~]# df -h
[root@ ~]# cd /var
[root@ ~]# du -sch *
Using the above commands, you can see that files and folders which consumes more size. You need to delete the unwanted files or folders and try reduce the size.
Try restart the MySQL service.
[root@ ]# /etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
Upgrade MySQL system tables
When you try to create a remote MySQL user, sometimes you may get the following the error.
ERROR 1146 (42S02): Table ‘mysql.procs_priv’ doesn’t exist
Why this error occurs?
MySQL system tables should be updated while upgrading MySQL version in the server and make sure that their structure is up to date. Otherwise, this error will occur when you create a remote user.
You can eliminate this error by executing the command in command prompt.
mysql_fix_privilege_tables –password=root_password
This command will update MySQL tables and its structure.
Note: Before executing this command, make sure that you have taken the full backup of MySQL.
MySQL: Access denied for user
MySQL Error : Error connecting to MySQL: Access denied for user: ‘root@localhost’ (Using password: YES)
This is mainly caused due to the fact that the user root does not have enough privileges to access the mysql databases or the password set for the user root to connect mysql was changed.
1. Start mysql using mysqld_safe
#/usr/local/etc/rc.d/mysqld stop
#mysqld_safe –skip-grant-tables &
Note:
mysqld_safe is used to start mysql server by disabling certain feature that restrict a user to access mysql. The option –skip-grant-tables is used to neglect the permission grant to different users of mysql.
2. Enter mysql prompt by just typing “mysql” and do the following
>GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY ‘newpassword’ WITH GRANT OPTION;
>FLUSH PRIVILEGES;
>\q
3. Kill all mysql processes and start mysqld
#killall -9 mysqld_safe
#killall -9 mysqld
#/usr/local/etc/rc.d/mysqld start
