Ubuntu or Debian can run multiple instances of PostgreSQL and provide a specific way to autostart/stop/start each cluster.
There should be a file named start.conf
inside /etc/postgresql/9.2/main
(or more generally /etc/postgresql/<version>/<clustername>) with these self-explanatory contents:
# Automatic startup configuration
# auto: automatically start/stop the cluster in the init script
# manual: do not start/stop in init scripts, but allow manual startup with
# pg_ctlcluster
# disabled: do not allow manual startup with pg_ctlcluster (this can be easily
# circumvented and is only meant to be a small protection for
# accidents).
auto
If you replace auto by manual, you could start this PostgreSQL instance only when desired with the command:
sudo pg_ctlcluster 9.2 main start
As for looking at the console, what you should want instead is having this run in a terminal when you work with the database:
tail -f /var/log/postgresql/postgresql-9.2-main.log
Root Cause
foreign_key_checks is not a global option you can preset at startup. Why ?
When you click on that link to the Documentation on foreign_key_checks, there is no chart that says it is global or session scope. Other options will specify the scope in a chart
With foreign_key_checks, it is session scope only.
Further proof of this is when you go to the command line and run this
mysqld --help --verbose > /tmp/mysqld.txt
vi /tmp/mysqld.txt
When you look inside the text file, you do not see --foreign-key-checks
as a command line startup option. The other four variables I showed you can be used on the command line and in my.cnf
.
If you have data to load, you can set that before loading:
SET foreign_key_checks = 0;
source mydata.sql
Proof of this is a standard mysqldump header ( first 10 lines )
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
Please note line 8
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
What does the bottom of a mysqldump look like ? Like this:
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
Please note the line
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
Epilogue
Bottom Line: foreign_key_checks is not meant of use in my.cnf
. That make the most sense because (if allowed) starting mysqld with that would be damaging to referential integrity from startup.
Best Answer
MariaDB use
systemd
as does MySQL. To disable MySQL or MariaDB, from starting on bootup, runIf you see
mysql.service
, try disabling that one first.Now you can start and stop MySQL with