They should all have these columns: Primary key: id , bigserial (which creates a sequence behind the scene) Date created: utc_created – Use current timestamp as a default value Date updated: utc_updated – Use a trigger to automatically update it (See code below) Creator: created_by – text type, can be anything Updator: updated_by – text …
Best practice: all Postgres tables can have these columns Read More »
Always use UTC as the timezone for postgre timestamp/date columns, which is considered to be the best practice and recommended by Postgre, e.g. And you java application is normally using LocalDateTime/LocalDate, if your system is not a global operation. So there needs to be a conversion in data access layer of you java application. According …
Timezone choice for Postgre and things to do in Java Read More »
Disclaimer: Not sure if they will surprise you A role can be a user This is STUPID ! It’s anti common sense. utf8 charset supports 4-byte chars In mysql you have to specify the charset to be “utf8mb4”, but in postgres it can just be “utf8” When you create a new database, any role is …
If you really want them work well, : 1. Make sure your mysql’s version is 5.6.5+ 2. The two columns exact data types must be createdAt timestamp not null DEFAULT CURRENT_TIMESTAMP, updatedAt timestamp null DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
So you don’t have to create an index manually on the foreign key columns.
There are many situations that multi-table data consistency can limit your work. For example, unit tests on single table , batch data migration and so on.
1. mac + dmg方式： /usr/local/mysql
数据备份对数据库本身有cpu压力；如果为了追求一致性，可能还要施加锁表操作，影响一定的可用性。数据量越大，这些代价就越高。 可以专门设置一个备库，然后从这个备库中把数据和binlog备份出来。
Logical Backup 就是用诸如mysqldump这样的工具把数据导出为可视的sql文件或csv文件。 Raw Backup 则是指直接备份数据库的数据文件。 Raw Backup执行起来比Logical Backup快的多，因为它不需要消耗CPU/内存把数据变成sql或csv(顺便说一下，变csv比变sql要快很多）。 但Raw Backup有一个缺点：Raw File的跨平台能力比较弱。 一个windows下的文件在linux下可能就用不了，一个从mysql 5.1导出的文件可能无法被mysql 5.5识别。 Raw Backup还有一个严重的缺点： 如果Raw File是一个Corrupted File, 复制后产生的备份仍是一个Corrupted File; 这个备份就没有作用，因为你没法利用它来恢复数据。
mysqldump默认情况下并不保证数据一致性。 在innodb中，可以这样： mysqldump –single-transaction mydb > mydb-backup.sql 如果有 –single-transaction参数，mysqldump会将整个数据的读取过程置于一个满足repeatable read的事务中，最后导出的数据也是相互一致的。 由于innodb的mvcc特性，使用 –single-transaction执行mysqldump, 不会阻塞其他进程的读写。