社区最近推出了Postgres 9.2.4版本,旨在打一个安全补丁,需要做一个升级,升级的步骤文档上也写得较为详细(Part 17),一般有pg_
社区最近推出了Postgres 9.2.4版本,旨在打一个安全补丁,需要做一个升级,升级的步骤文档上也写得较为详细(Part 17),一般有pg_upgrade和dump/restore两种方式,对9.2.0及以上版本采用直接编译安装的方式即可,,9.2.0以下版本还需要dump/restore方式,中间会涉及停机操作,做好业务沟通工作。本次升级,数据量较大的版本是9.2.0,其他小版本的数据量相对不大,故采用后一种方式升级。
步骤 :
1.备份
pg_dump -h 192.168.2.150 -p 5432 -U postgres -b -Fp db_ -f db.bak
安全起见,备份出来的文件检查一下是否正常,能否正确导入,最好记一下用户名及密码等
2.关闭服务
pg_stop
ps -ef|grep postgres 检查一下
3.编译postgresql_9.2.4,原先是9.1.3
[postgres@ ~] $tar -zxvf postgresql_9.2.4.tar.gz
[postgres@ ~] cd postgresql_9.2.4
[postgres@ postgresql_9.2.4]./configure --prefix=/home/postgres --with-pgport=5432 --with-segsize=8 --with-wal-segsize=64 --with-wal-blocksize=64 --with-perl --with-python --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety
[postgres@ postgresql_9.2.4]gmake world
[postgres@ postgresql_9.2.4]gmake install-world
如果顺利的话,这个时候可以看一下BIN是否已经换成9.2.4
[postgres@ postgresql_9.2.4]$ psql -V
psql (PostgreSQL) 9.2.4
[postgres@ postgresql_9.2.4]$
说明BIN文件等已经OK
如果原始版本是9.2.0及以上,可忽略以下步骤,直接启动postgres,如果是9.2.0以下版本,直接启动会报错,则继续,类似initdb与configure版本不一致的问题
4.修改数据文件
[postgres@ ~]mv /database/pgdata /database/pgdata.old
[postgres@ ~]mkdir /database/pgdata
5.初始化DB
[postgres@ ~]$initdb -D /database/pgdata -E UTF8 --locale=C -U postgres -W
6.拷贝老的postgresql.conf与pg_hba.conf文件到新的$PGDATA下
[postgres@ ~]$cp /database/pgdata.old/postgresql.conf /database/pgdata/postgresql.conf
[postgres@ ~]$cp /database/pgdata.old/hba.conf /database/pgdata/hba.conf
7.启动数据库
pg_start
--如果是单个库导出恢复则建一下DB和相关的用户,省得导入报错
8.恢复数据
[postgres@ ~]$psql -d db_ -f db.bak
9.检查
检查数据、应用及日志是否有异常,还有一些容易遗漏的参数如timezone等,这个可以通过数据库里select now()与系统时间是否匹配来检查还有一种不用数据dump导出的pg_upgrade方式,后续再记。
就这样吧。

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

Scenarios where PostgreSQL is chosen instead of MySQL include: 1) complex queries and advanced SQL functions, 2) strict data integrity and ACID compliance, 3) advanced spatial functions are required, and 4) high performance is required when processing large data sets. PostgreSQL performs well in these aspects and is suitable for projects that require complex data processing and high data integrity.

The security of MySQL database can be achieved through the following measures: 1. User permission management: Strictly control access rights through CREATEUSER and GRANT commands. 2. Encrypted transmission: Configure SSL/TLS to ensure data transmission security. 3. Database backup and recovery: Use mysqldump or mysqlpump to regularly backup data. 4. Advanced security policy: Use a firewall to restrict access and enable audit logging operations. 5. Performance optimization and best practices: Take into account both safety and performance through indexing and query optimization and regular maintenance.

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Notepad++7.3.1
Easy-to-use and free code editor

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
