PostgreSQL 内存数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1828895/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL In Memory Database
提问by David Barnes
I want to run my PostgreSQL database server from memory. The reason is that on my new server, I have 24 GB of memory, and hardly any of it is used.
我想从内存中运行我的 PostgreSQL 数据库服务器。原因是在我的新服务器上，我有 24 GB 的内存，几乎没有使用。
I know I can run this command to make a ramdisk:
我知道我可以运行这个命令来制作一个 ramdisk：
mdmfs -s 1024m md2 /mnt
And I could theoretically have PostgreSQL store its data there. But the problem with this is that if the server crashes or reboots, the data will be gone.
理论上我可以让 PostgreSQL 在那里存储它的数据。但问题在于，如果服务器崩溃或重新启动，数据将消失。
Basically, I want the database to be loaded in memory at all times so that it does not have to go to the hard disk drive to read every record, since I have TONS of memory and since memory is faster than hard disk drives.
基本上，我希望数据库始终加载到内存中，这样它就不必去硬盘驱动器读取每条记录，因为我有大量内存，而且内存比硬盘驱动器快。
Is there a way to do this while also having PostgreSQL write to disk so I don't lose any data in case the server goes down? Or is there a way to cache all data in memory?
有没有办法做到这一点，同时还让 PostgreSQL 写入磁盘，这样我就不会在服务器出现故障时丢失任何数据？或者有没有办法将所有数据缓存在内存中？
回答by TonyC
I'm now using streaming replication which is async. This means my MASTER could be running all in memory, with the separate SLAVE instance using traditional disk.
我现在正在使用异步的流式复制。这意味着我的 MASTER 可以全部在内存中运行，单独的 SLAVE 实例使用传统磁盘。
A machine restart would involve stopping the SLAVE, copying the postgresql data back into ramdisk and then restarting the MASTER followed by the SLAVE. This would be an interesting possibility which compares well with something like REDIS, but with the advantage of redundancy / hotstandby / backup / sql / rich toolset etc.
机器重启将涉及停止 SLAVE，将 postgresql 数据复制回 ramdisk，然后重新启动 MASTER，然后是 SLAVE。这将是一个有趣的可能性，与 REDIS 之类的东西相比很好，但具有冗余/热备用/备份/sql/丰富的工具集等优势。
回答by just somebody
have you seen the Server Configuration manual chapter? check it out, then google postgresql memory tuning.
你看过服务器配置手册章节吗？检查出来，然后谷歌postgresql memory tuning。
回答by Robert Harvey
I have to believe that Postgres is written in such a way as to take full advantage of available RAM in the server. As you may have guessed by now, there's no reliable way to do this outside of Postgres.
我必须相信 Postgres 的编写方式可以充分利用服务器中的可用 RAM。正如您现在可能已经猜到的那样，在 Postgres 之外没有可靠的方法来做到这一点。
Within Postgres, transactions assure that all operations are atomic, so if the power goes down while you are writing to a Postgres database, you will only lose that particular operation, and not the entire database.
在 Postgres 中，事务确保所有操作都是原子的，因此如果在写入 Postgres 数据库时断电，您只会丢失该特定操作，而不是整个数据库。
回答by Grant Johnson
The answer is caching. Look into adding memory to the server, then tuning PostgreSQL to maximize memory usage. Also, the file system cache will help with this, doing some of it automatically. You will be able to speed up performance, almost as if it were in memory except for the first hit, while not having to manage it yourself, and being able to have a database larger than the physical memory.
答案是缓存。考虑向服务器添加内存，然后调整 PostgreSQL 以最大化内存使用。此外，文件系统缓存将对此有所帮助，它会自动执行其中的一些操作。您将能够提高性能，除了第一次命中外，几乎就像在内存中一样，而不必自己管理它，并且能够拥有比物理内存更大的数据库。
