Category Archives: Database

Best practice: all Postgres tables can have these columns

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 […]

Timezone choice for Postgre and things to do in Java

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 […]

Surprising features of postgres

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 […]

MySQL两种备份模式:Logical Backup 和 Raw Backup

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; 这个备份就没有作用,因为你没法利用它来恢复数据。

对InnoDb执行mysqldump时应该加上 –single-transaction参数

mysqldump默认情况下并不保证数据一致性。 在innodb中,可以这样: mysqldump –single-transaction mydb > mydb-backup.sql 如果有 –single-transaction参数,mysqldump会将整个数据的读取过程置于一个满足repeatable read的事务中,最后导出的数据也是相互一致的。 由于innodb的mvcc特性,使用 –single-transaction执行mysqldump, 不会阻塞其他进程的读写。