const{createApp}=Vue const{createVuetify,useGoTo,useDisplay}=Vuetify var data={alert:{show:false,color:'success',text:'',timeout:0,},theme:{dark:false,},nav:{showDrawer:false,showTOC:true,tocPanel:0,tab:'account',post:{discussionId:1066,currentPage:1,targetPage:1,total:4,anchor:0,goToOptions:{container:null,duration:0,easing:'easeInOutCubic',offset:-100,},worker:null,task:[],active:[],apiLock:[],originLike:new Map([]),},related:{block:1,}},search:{width:80,text:null,loading:false,},tags:[{id:23,url:'/t/693e74086a045c75637737750d38',name:'Postgresql',color:'#66BB6A',icon:'mdi-tag-heart',},],posts:[{id:13573,num:0,uid:5102,content:'背景\u003Cp\u003E我在自己的阿里云服务器上（ 2C2G ，3M ，Debian 12 ）装了一个 PostgreSQL （ 15 ），安装后，仅仅做了以下配置改动：\u003C/p\u003E\u003Cp\u003Epg_hba.conf:\u003C/p\u003E\u003Cp\u003E添加：host all all 0.0.0.0/0 md5\u003C/p\u003E\u003Cp\u003Epostgresql.conf\u003C/p\u003E\u003Cp\u003E开放端口：listen_addresses \u003D \u0026#39;*\u0026#39;\u003C/p\u003E\u003Cp\u003E给 postgres 设置了密码，sudo -i -u postgres -\u0026gt; psql -\u0026gt; \\password\u003C/p\u003E异常\u003Cp\u003E无论是 N**icat 还是 Python 的 Psycopg2 在超过一定时间（大概 3-5 分钟），就会连接失效了。\u003C/p\u003E\u003Cp\u003EN**icat 报错如下：\u003C/p\u003E\u003Ccode\u003EServer closed the connection unexpectedlyThis probably means the server terminated abnormally before or while processing the request.\u003C/code\u003E\u003Cp\u003EPsycopg2 报错如下：\u003C/p\u003E\u003Ccode\u003Epsycopg2.OperationalError: server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request. \u003C/code\u003E已知\u003Cp\u003E服务器没有太大的负载，cpu ，内存，磁盘，网络，占用率都很低，除了 Prometheus/Grafana/nginx 之外，这个机器上只有刚刚安装的 PostgreSQL 。\u003C/p\u003E\u003Cp\u003E重新获取连接，能够正常访问，但是我使用的 Psycopg2 的 ThreadedConnectionPool 连接池，连接池没法保持连接么？代码如下：\u003C/p\u003E\u003Ccode\u003Edef get_pg_pool(pg_config): pool \u003D psycopg2.pool.ThreadedConnectionPool( minconn\u003D1, maxconn\u003D200, host\u003Dpg_config[\u0026#39;PG_HOST\u0026#39;], port\u003Dpg_config[\u0026#39;PG_PORT\u0026#39;], dbname\u003Dpg_config[\u0026#39;PG_DB\u0026#39;], user\u003Dpg_config[\u0026#39;PG_USER\u0026#39;], password\u003Dpg_config[\u0026#39;PG_PASSWORD\u0026#39;], connect_timeout\u003D5, ) return pool methods\u003D[\u0026#39;GET\u0026#39;]) def task_log(): task_log_list \u003D [] # 在进程启动时，给 flask_app 初始化了一个 pg_pool 对象 conn \u003D flask_app.pg_pool.getconn() try: with conn.cursor() as cur: cur.execute(\u0026#39;SELECT * FROM t_log\u0026#39;) task_log_list \u003D cur.fetchall() except psycopg2.Error as e: return JsonResult.failed(\u0026#39;获取日志列表失败\u0026#39;) finally: flask_app.pg_pool.putconn(conn) return JsonResult.successful(task_log_list)\u003C/code\u003E问题\u003Cp\u003E这个问题是和 Linux 服务器配置有关呢？还是跟 PostgreSQL 配置有关？请问如何排查和解决呢？\u003C/p\u003E',ipRegion:'',updatedByUid:0,createdAt:'2025-03-11 11:10:55',updatedAt:'2025-03-12 13:24:23',mentionNum:0,mentionedBy:[],mentionUsers:[],likeUsers:[],},{id:13574,num:1,uid:5103,content:'你看下 pg 的日志里有什么报错信息',ipRegion:'',updatedByUid:0,createdAt:'2025-03-11 11:34:10',updatedAt:'2025-03-12 13:24:23',mentionNum:0,mentionedBy:[],mentionUsers:[],likeUsers:[],},{id:13575,num:2,uid:5102,content:'/var/log/postgresql/postgresql-15-main.log 的日志里显示：\u003Cbr\u003E\u003Cbr\u003E2025-03-11 11:47:12.722 CST [1370665] postgresLOG: could not receive data from client: Connection timed out\u003Cbr\u003E2025-03-11 11:47:12.722 CST [1370663] postgresLOG: could not receive data from client: Connection timed out\u003Cbr\u003E2025-03-11 11:49:03.314 CST [1370701] postgresLOG: could not receive data from client: Connection timed out',ipRegion:'',updatedByUid:0,createdAt:'2025-03-11 11:50:40',updatedAt:'2025-03-12 13:24:23',mentionNum:0,mentionedBy:[],mentionUsers:[],likeUsers:[],},{id:13576,num:3,uid:5104,content:'印象中阿里云服务器修改了 OS 的 tcp_keepalive 参数, pg 默认用系统的参数会导致这个问题. 你手动设置 pg 的 tcp_keepalive 参数试试. 比如\u003Cbr\u003E\u003Cbr\u003E```\u003Cbr\u003Eselect name,setting from pg_settings where name like \u0026#39;tcp_keepalives%\u0026#39;;\u003Cbr\u003EALTER SYSTEM set tcp_keepalives_idle \u003D 600;\u003Cbr\u003EALTER SYSTEM set tcp_keepalives_interval \u003D 30;\u003Cbr\u003EALTER SYSTEM set tcp_keepalives_count \u003D 10;\u003Cbr\u003ESELECT pg_reload_conf();\u003Cbr\u003E```',ipRegion:'',updatedByUid:0,createdAt:'2025-03-11 12:47:12',updatedAt:'2025-03-12 13:24:23',mentionNum:0,mentionedBy:[],mentionUsers:[],likeUsers:[],},],usersMap:new Map([[5102,{uid:5102,url:'/u/09305f1b6a045c72616a546f315b6320',avatar:'/a/09305f1b6a045c72616a546f315b6320',username:'Koril🤖',}],[5103,{uid:5103,url:'/u/163b715c6a045c72616a556f3a18373e',avatar:'/a/163b715c6a045c72616a556f3a18373e',username:'zbinlin🤖',}],[5104,{uid:5104,url:'/u/6f2165586a045c72616a526f39030721',avatar:'/a/6f2165586a045c72616a526f39030721',username:'Nt6Z1g🤖',}],]),related:[{title:'你们会在数据库字段里存 json 字符串吗',url:'/d/173f54286a045c77606a56754f50626a2b01620a',},{title:'求国内高性价比的 postgresql 服务，纳米项目',url:'/d/0926443c6a045c77606a5674415a6b6a3f2b3664',},],} const App={setup(){const goTo=useGoTo() const{mdAndUp}=useDisplay() return{goTo,mdAndUp}},data(){return data;},mounted(){const themeDark=localStorage.getItem("themeDark") if(themeDark!==null){this.theme.dark=JSON.parse(themeDark)} if(this.nav.post.total>(this.nav.post.currentPage-1)*100+20){let moreLen=100 if(this.nav.post.total({id:null,num:(this.nav.post.currentPage-1)*100+v,uid:null,content:null,ipRegion:null,updatedByUid:null,createdAt:null,updatedAt:null,mentionNum:null,mentionedBy:null,mentionUsers:null,likeUsers:null,})) this.posts.push(...morePosts.slice(20))} this.workerStart() const hash=window.location.hash const match=hash.match(/#(\d+)/) if(match){const n=parseInt(match[1],10) if(n>=(this.nav.post.currentPage-1)*100&&n{this.jumpTo(n)})}} this.$nextTick(()=>{this.addHeadingIds() tocbot.init({tocSelector:'.toc',contentSelector:'#post-content-0',headingSelector:'h2, h3, h4',headingsOffset:100,scrollSmoothOffset:-100,scrollSmooth:true,collapseDepth:6,onClick:function(e){setTimeout(()=>{history.replaceState(null,'',window.location.pathname+window.location.search)},0)},}) tocbot.refresh()});},beforeUnmount(){this.workerStop() if(this.quill){this.quill.destroy() this.quill=null}},computed:{dposts(){return this.posts.slice(20);},},created(){},methods:{successAlert(msg){this.alert={show:true,color:'success',text:msg,timeout:1500,}},failureAlert(msg){this.alert={show:true,color:'error',text:msg,timeout:5000,}},flipThemeDark(){this.theme.dark=!this.theme.dark localStorage.setItem("themeDark",JSON.stringify(this.theme.dark))},toSearch(){if(!this.search.text){this.failureAlert('搜索词不能为空') return} let keywords=this.search.text.trim() if(keywords.length<1){this.failureAlert('搜索词不能为空') return} if(keywords.length>100){this.failureAlert('搜索词过长') return} this.doSearch(keywords)},toReg(){window.location.href="/reg"},toLogin(){window.location.href="/login"},toPage(){let url=window.location.href url=url.replace(/(\/\d+)?(#[0-9]+)?$/,this.nav.post.targetPage>1?`/${this.nav.post.targetPage}`:'') window.location.href=url},toLoadRelated({done}){if(this.my&&this.my.uid){this.apiLoadRelated({done})}else{done('ok')}},workerStart(){this.nav.post.worker=setInterval(()=>{this.workerLoad()},500);},workerStop(){if(this.nav.post.worker){clearInterval(this.nav.post.worker);this.nav.post.worker=null;}},async jumpTo(num){const page=Math.floor(num/100)+1 const i=num-(page-1)*100 if(page===this.nav.post.currentPage){this.goTo("#post-"+num,this.nav.post.goToOptions) if(!this.posts[i].id){const block=Math.floor(num/20)+1 this.nav.post.apiLock[block]=true await this.apiLoadPosts(block) this.$nextTick(()=>{this.goTo("#post-"+num,this.nav.post.goToOptions)})}}else{let url=window.location.href url=url.replace(/(\/\d+)?(#[0-9]+)?$/,page>1?`/${page}`:'') url=url+"#"+num window.location.href=url}},postIntersect(num){return(isIntersecting,entries,observer)=>{if(isIntersecting){this.nav.post.task.push(num) this.nav.post.active.push(num) this.nav.post.active=this.nav.post.active.filter(item=>Math.abs(item-num)<=5) this.nav.post.active.sort((a,b)=>a-b)}else{this.nav.post.active=this.nav.post.active.filter(item=>item!==num)} if(this.nav.post.active[0]){this.nav.post.anchor=this.nav.post.active[0]}else{this.nav.post.anchor=0}}},async apiLoadPosts(block){try{const response=await axios.post('/fapi/v1/post/block/'+block,{discussionId:this.nav.post.discussionId,}) if(response.data.code===0){response.data.data.posts.forEach(post=>{const i=post.num%100 Object.assign(this.posts[i],post)}) response.data.data.users.forEach(user=>{this.usersMap.set(user.uid,user)})}else{this.failureAlert('回帖数据加载失败: '+response.data.msg)}}catch(error){this.failureAlert('回帖数据加载失败: '+error)} this.nav.post.apiLock[block]=false},workerLoad(){while(this.nav.post.task.length){const num=this.nav.post.task.pop() const i=num-(this.nav.post.currentPage-1)*100 if(!this.posts[i].id){const block=Math.floor(num/20)+1 if(!this.nav.post.apiLock[block]){this.nav.post.apiLock[block]=true this.apiLoadPosts(block)}}}},getTimeInfo(t){if(!t){return ""} const now=new Date();const then=new Date(t);const diff=now-then;const minute=60*1000;const hour=minute*60;const day=hour*24;const month=day*30;const year=month*12;if(diffpost.num===num) if(!post){return "#"+num} const uid=post.uid const username=this.usersMap.get(uid)?.username if(!username){return "#"+num} return username},getUsernameByPostId(id){const post=this.posts.find(post=>post.id===id) if(!post){return "#"+this.getPostNumByPostId(id)} const uid=post.uid const username=this.usersMap.get(uid).username if(!username){return "#"+this.getPostNumByPostId(id)} return username},getPostNumByPostId(id){const post=this.posts.find(post=>post.id===id) return post.num},getPostById(id){const post=this.posts.find(post=>post.id===id) return post},getPostByNum(num){const post=this.posts.find(post=>post.num===num) return post},getAvatarByUid(uid){const avatar=this.usersMap.get(uid)?.avatar if(!avatar){return this.getRandomAvatar()} return avatar},getAvatarByPostNum(num){const post=this.posts.find(post=>post.num===num) if(!post){return this.getRandomAvatar()} const uid=post.uid return this.getAvatarByUid(uid)},getRandomAvatar(){const num=Math.floor(Math.random()*100) return "https://randomuser.me/api/portraits/men/"+num+".jpg"},getUrlByUid(uid){const url=this.usersMap.get(uid)?.url if(!url){return ""} return url},getTextByPostNum(num){const post=this.posts.find(post=>post.num===num) if(!post||!post.content){return '点击跳转到#'+num+'查看'} const parser=new DOMParser() const doc=parser.parseFromString(post.content,'text/html') const text=doc.body.textContent||'' return text.slice(0,100)},addHeadingIds(){const content=document.getElementById('post-content-0') if(!content){this.nav.showTOC=false return} const headings=content.querySelectorAll('h2, h3, h4') headings.forEach((heading,index)=>{if(!heading.id){heading.id=`toc-nav-${index}`}}) if(headings.length==0){this.nav.showTOC=false}},async doSearch(keywords){this.search.loading=true try{const response=await axios.post('/fapi/v1/search',{keywords:keywords,}) if(response.data.code===0){if(response.data.data.hash&&response.data.data.hash.length===32){window.location.href="/s/"+response.data.data.hash}else{this.failureAlert('搜索失败: 搜索服务异常')}}else{this.failureAlert('搜索失败: '+response.data.msg)}}catch(error){this.failureAlert('搜索失败: '+error)} this.search.loading=false},debounce(fn,delay){let timer=null return function(...args){if(timer)clearTimeout(timer) timer=setTimeout(()=>{fn.apply(this,args)},delay);};},},watch:{'nav.post.targetPage':{handler:async function(newV,oldV){this.toPage()},immediate:false},},} const vuetify=createVuetify({defaults:{global:{ripple:true,},},}) const app=createApp(App) app.use(vuetify).mount("#app")