Skip to content
PodSubs
ShowsPeopleGuestsJobsNewsEvents
More
BlogPodCred
Menu
ShowsPeopleGuestsJobsNewsEventsBlogPodCredNetworksSign in
Join nowSign in
  • Shows
  • People
  • Jobs
  • News
  • More
    • Guests
    • Events
    • Blog
    • PodCred
    • Networks
    Join nowSign in

Postgres FM

Postgres FM artwork

Advanced psql

Oct 25, 202453 minAudio

Nikolay and Michael discuss some cool things you can do with psql, the official CLI that ships with Postgres. Here are some links to things they mentioned: psql docs https://www.postgresql.org/docs/current/app-psql.html Our episode on psql vs GUIs https://postgres.fm/episodes/psql-vs-guis postgres_dba https://github.com/NikolayS/postgres_dba Our episode on massive deletes https://postgres.fm/episodes/massive-deletes Postgres hacking session on \watch with limited number of loops https://www.youtube.com/watch?v=vTV8XhWf3mo pspg https://github.com/okbob/pspg Our episode on Postgres gotchas https://postgres.fm/episodes/postgres-gotchas current_setting() and set_config() docs https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET psql tips (site by Lætitia Avrot) https://psql-tips.org ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With special thanks to: Jessie Draws for the elephant artwork

Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.

Transcript

From the show. Tap a time to play from there.

Nikolay: Hello, hello, this is Postgres.FM. I'm Nikolay from Postgres.AI, and as usual with me here Michael from pgMustard. Hi, Michael. Michael: How are you, Nikolay? I'm good, how are you? Okay. No, how are you? Nikolay: I'm very good, I'm very good. Okay, so we are going to discuss psql once again. Last time we compared it to graphical UIs, graphical tools.

And this, like, it was my idea to discuss it once again, but just purely just psql. How to use it in various cases, why use it, and and maybe some tips and tricks, right? Michael: Yeah, looking forward to this 1. You say last time we talked about it but it was like 18 months ago Nikolay: yeah I was trying to say we discussed it last time I mean in this area yeah and we discussed psql particularly right

I Michael: just couldn't believe it when I looked it up how long ago that was. So yeah, Nikolay: your mind is focused, is targeted to find gotchas everywhere since last episode and You try to find it in my speech now. Okay, this is good. But just for today, let's focus on psql. And I have some experience, I like it, which is good, right, for conversation. You said you don't have a lot of experience with it, but you may be better prepared as usual so maybe you know some recent

changes or something. Where can we start? Let's maybe discuss why, right? Because we have Python, we have various tools. Why psql? What's your answer to this? Michael: Well, yeah, so I don't feel like I'm an advanced user of psql, personally. I have to use, well, I try to use it sometimes, I try to keep familiar with it so that if I need to. The nice thing about psql is it's always available.

We talked about this last time. But if it's somebody else's database or it's my own, it's always available if I need to do it from my mobile, via like, yeah, so if I like a console, I can do that. It's very accessible. But I don't feel like I'm an advanced user the reason I've had to get more familiar with it as well there is like helping diagnose issues, so I see a lot of query plans and query plan formatting can vary quite a lot depending on the editor and psql or psql has a bunch of formatting and alignment options and different

pages and each 1 has its own formatting quirks so supporting all those different formats for people copy and pasting, EXPLAIN plans has given me some familiarity as well. But yeah, on the coding side of things, I don't have much experience at all. But I did check out what was the program called? Is it postgres_dba? postgres_dba? Nikolay: postgres_dba, yeah. This is a bunch of tools and interactive menu I've built using some tricks in psql. It was long ago, but yeah, I still use it sometimes and some

people use it. It's quite interesting. Right. So it sounds like you say it's always available and I partially agree. It's available if you install Postgres with client packages. If you install only server packages or if you are a user of RDS, it's not available, right? Or maybe user of, I don't know, Supabase. psql, maybe it's not that natural because there is editor in,

or new 1, for example, there is an editor right in browser and maybe it's closer for this particular packaging, I don't know, like, there's no psql by default there, right? Michael: Some cloud providers you can, so I know on Google Cloud, which is what I use myself, you can, there's a console, there's a cloud console that you can have that you can get access to Nikolay: that's good yeah Michael: yeah some you can Nikolay: and I think this is not they do it not bringing psql

to browser but maybe like emulating console running on server. This is what I suspect. Michael: I assume so, yeah. Nikolay: Yeah, this is good, but others don't do this, unfortunately, for me. I would prefer everyone to provide a psql and make it available like it is when you deal with pure open source Postgres. Right? So, to me, the reason why psql, there's a bunch of reasons.

First of all, it's the only official and well-maintained client. If we consider all the clients regardless, like terminal-based or graphical, doesn't matter. This is the only officially maintained client. Well, if we don't count pgbench as client, right? Because it's also a client but it's very specific. It's very specific, it's also official, well-maintained, somehow included to server packaging for Ubuntu and Debian, I don't know why, unlike psql.

But it's not a regular client because it's needed for different tasks, for benchmarks, for research of performance and so on. psql is a universal client, terminal-based, and this is the only 1. There is no graphical tools, graphical interfaces officially supported by Postgres itself. Postgres shipped with Postgres and so on. And pgAdmin is also a third-party tool.

Which means the release cycle, quality, features, you can see changes, new stuff added to psql, in release notes of Postgres itself, this is good. Quality and also trust. So when you say explain plans formatting can be very strange. Well, from psql, you actually said it's from psql can be very strange, but I'm going on purpose to mix psql and psql as we

discussed, right? Because I don't care about this. Michael: Same. Nikolay: Yeah, so this is specific case plans, right? I know this pain of bad formatting, shitty formatting, and you need to deal with it, and it's like, it's a lot of pain. But in all other areas, when you do something, you expect behavior from psql, like the true behavior, right? I can give you an example. I had some small project to convert something.

I don't remember. Maybe to DELETE many million rows or convert some table to something. I don't remember. Maybe it was int4 (primary key conversion). So we decided to code it purely in psql and SQL. So you just combine files. I like to name such files dot psql. So it emphasizes that it's not only pure SQL, but they can have some commons only psql supports, like \if, for example, and we will talk about it in a few minutes.

And something was interesting, like some query was working there, everything as expected. We had tests, all good. But attempt by some other developers in that company, it was a huge client. They went to IPR like a few months after we started working with them. It was great. And they coded like similar stuff, but in their system. It was Java, actually. And same queries didn't work well. Like, I mean, they didn't work at all. And when I started troubleshooting, I noticed that in 1 of the

graphical UIs, it's absolutely the same behavior as in their code. And that graphical program was also built using Java. So I realized it's JDBC. It was something with float, rounding, rules or something. It was very different behavior compared to what I saw in psql and this discrepancy in behavior it can be big deal if you want to like to have something reliable and supported for many years,

it's good to deal with psql. Michael: That's a great point. I use it for debugging purposes as well. Like, it helps rule something out. If I can, if I am seeing some behavior from it via my application or via a graphical user interface, and it behaves the same in psql, I'm gaining confidence that there's a problem. It helps rule out. I assume there's not a bug in psql. That's a default assumption of mine. I'm probably wrong, probably gonna trip me up 1 day, but it hasn't

so far. Nikolay: Yeah, sitting in psql, talking to Postgres, you're basically closer to Postgres, right? There is no... JDBC adds something and if it's something, a third-party tool, it also adds something and this something, like these layers, can change what you see. And this can be a bug or intentional, but this I always like, it's like with monitoring, if I see some graph, like I don't understand.

Unless I see the code, how the information was gathered and processed, I don't understand what is presented. Trust is not high in this area. There's always doubt. Am I seeing this? This is spike really like what I'm thinking it is, right? So this is about rust and so on, and also features, like features are great. And I'm sure, I don't know all of them. I'm constantly learning and finding new stuff which I was not

aware of for many years. So these are my basic ideas why I use it a lot. Michael: Yeah, so actually I had a question for you on that front. When I was looking through the docs page for psql, like the number of options and flags and it's just so many when you're working with it how do you learn about new features do you think I wonder if it can do X and then you look up can it do X or is it more a case of every now and again you'll flick through and think

how yeah how do you learn more about it Nikolay: yeah it's a good question I think it's a mix so sometimes I just know what is possible and I don't remember I my favorite comment is \? and \H for help with SQL and there's a lot of documentation, it's right there. So help and the grammar of SQL itself, it's there. But sometimes I see something which I'm thinking, is it possible?

For example, if we want to process a lot of rows and batches, of course, it's good to write some full-fledged program using Python or Go or anything, with monitoring, logging, but if you need it quickly, my usual approach, like ad hoc processing, it's needed really soon. We need the result right now. I just write a psql code using \watch. Right? And I was always like, okay, \watch is good.

Like it's for looping, right? You run some query which takes a batch and process it. For example, deleting, right? We need to clean up some data. And we cannot delete in Postgres. It's a very bad idea to delete many millions of rows in a single transaction. Michael: We did an episode. Nikolay: Yeah, yeah. So massive delete can hit you. If you need to delete like millions of rows in a huge table, it's better to do it in batches. And it's easy to write some CTE.

Quite easy. To write some CTE, finding the scope of work right now, like a thousand rows or something, delete them and report in a nice way maybe with even with progress bar which wishes like this what I like and not not to forget about vacuum of course and dead tuples and processing Michael: we took yeah Nikolay: yeah yeah yeah Michael: but but okay back to so we're talking about \watch back to us \watch right badly named probably What do you think of the name? Nikolay: \watch? Michael: Yeah.

Well, yeah. It was created, I think, to, you know, like you have a select from just activity. You, for example, aggregate queries by state, understand how many active backends we have, maybe wait events as well, and you just observe them. And that's why \watch, I think. Michael: I get it. But repeat or something that gives it a little bit more of like an idea of what it's actually going to do. Nikolay: Your mind definitely is targeted to find gotchas. I'm with you here.

It's not perfect naming at all. Yeah. 100%. Michael: So yeah, super cool feature. I've even seen it used, the time I saw it used most recently was I was watching a Patroni demo for when I was preparing for a few episodes ago. Alexander Kukushkin used \watch in 1 session to keep querying a database like while trying to fail over to show latencies and to show what happened like did any queries actually fail did it like what was the what was the latency of the failover that

kind of thing it was a really cool use case to continually query a database Nikolay: right right exactly. \watch is useful, but it's until, I think it was not until Postgres 16 when we had, with Andrei and Kirk, we had Postgres hacking sessions, and we extended it to support the number of loops you want. Because the only option is the sleep time between your queries.

In the documentation of another gotcha, I think it was described as interval time. But if you take into consideration the duration of query itself, it's not counted. So it's only sleep time after 1 comment finished before running the next 1. And the idea was, before Postgres 16, the only option was to specify time. By default, I think it's 2 seconds. I don't know why 2 seconds. Specify some time.

You can specify 0.1, 100 milliseconds slip time, like very quickly running queries in a loop, and it's infinite, unless it fails. If it fails, it stops. By the way, it could be an option again. I'm just thinking right now, maybe this should be an option saying, if it fails, still continue. Right? Because... Michael: Interesting. Nikolay: Yeah, default behavior is just to stop. In some cases, I would prefer continue. And like, right now I have such situations when I need even if

it fails, still continue with the loop. In this case, I... Michael: Oh, like the failover testing. If you're doing failover testing, even if 1 of them fails... Like in Kukushkin's case. Yes, exactly. Nikolay: Exactly, exactly. But \watch behaves like it just stops. And if I need this behavior, I just need to go to like shell level, bash or any like ZSH and there I need to bash usually because I don't run such things from my laptop. I'm usually running everything in tmux right on the server or

very close to the server in cloud. So if like California internet is very bad, you know it. And even Starlink sometimes is down. So in this case, I'm not losing my session. tmux is great. And if query fails, if you need to continue, I'm forced to go to shell while sleep 1, for example. Not while true, because if you do while true, ctrl-c won't work properly in many cases. It's very annoying if you write while...

If you sleep first, then do something. So while sleep 1, well, how many seconds you want to have between running psql. In this case, Control-C will work when you need to interrupt it. And in this case also you can have some additional stuff saying if comment is failed, for example, vertical bars, a couple of vertical bars, or report failed and then continue the loop, right? Michael: I was reading through what the, well, you're mentioning some improvements that have happened in psql recently, which is

cool, but I think it's not necessarily obvious that it's continuing to improve. And I did notice, I didn't read into the details, but somebody improved it recently to make Control-C work better. So I think maybe in 1 of the more recent well I'm not sure it fixes that Nikolay: I'm not sure it's related it's well it's interesting maybe maybe I need to check it's it's all it was in Postgres 17 release notes right yeah I also remember something, but I didn't connect

dots here. So let's unwrap my story, because we have already... It's amazing, right? So the idea was we want to be able to specify how many loops we need. And to do that we extended basically this tiny grammar, right, or tiny format, and allow to specify basically any options, named options. So we added like the... We converted this not interval, how to properly say, the break

between sleep time between 2 commands executed. Michael: Sleep time is good. Nikolay: Yeah, I think we can call it sleep. By the way, since I don't see Postgres 16, it was released in 2016, I don't see 16 too often in production. I still have not got used to this, what we developed, right? It's interesting. So I still, mostly all the time, I use psql. And I must correct myself, actually. In many cases, we have psql already 16, even if we work with

old server. So it's just my mind problem. I need to adjust my habits. I need to adjust my habits and start using what we developed. But the second option was, and this is the whole purpose of that work, is to allow us to specify the number of loops you need. If you know you don't need more than something, that's it. But the interesting fact is that the inspiration of that, yeah, I'm checking, it's interval I, number of seconds, it's wrong naming, but it is interval.

With understanding it's not actually interval, it's interval not taking into account the duration of the comment executed, which can take actually minutes or hours in extreme cases. And yes, c or count equals something. This is the number of loops you need, right? And that's great. But actually, the original idea why we thought about this was... Many times I had work, I process a lot of rows and batches, and

I need to stop. And to stop I usually used division by 0. This is a very old trick, it works. I know the \watch will stop if there is an error. So I just... When there is nothing to process anymore I just divide by number of rows to process. It's 0 so it stops because of division by 0. And interesting, then in Postgres 17, it was implemented, right? Already not by us, by Greg Sabino Mullane, I think. Yeah, allow psql \watch to stop after minimum number of rows return.

Funny thing, this is exactly what I needed. But we implemented some different parts around this problem. So you now can say m or min rows, mir underscore rows equals some number of rows. And you can say 0. If we have 0, that's it. This is great, right? Michael: Yeah, kind of a weird name. Is it 0 or is it 1? Nikolay: A number of rows returned. If you return, for example, if you delete returning star and

it's returned 0, then you need to stop. Oh, you think it should be... Yeah, well, let's double check but let's not to let's not to be like super we are not providing some like lesson or reference we are talking what's possible. There is documentation and also you can try and learn. So now it's possible, you don't need division by 0 anymore. The only issue with this I have is that I did like, and I still do like, my approach reporting, like, you know, progress bar

and so on, many stuff, many pieces of information. When you process a batch, you report a lot of stuff. In this case, I cannot use this. I cannot say... I always have some rows reported in the result. Usually just 1 row, many columns, like number of rows we processed, what's left, like progress, percentage, anything. So I think will I still use division by 0 after I got used to Postgres 17.

I'm not sure, right? Michael: Should we move on? Nikolay: Yeah, let's move on. And I wanted to emphasize there are a couple of areas. There are 2 big areas. First big area where we can use Psycal. First big area is interactive mode. And sometimes we use \watch there for just observing something. This is where name plays well. Or we use like various advanced stuff. I got used to \gx instead of semicolon.

By the way, both \watch and \gx don't require semicolon. I know even Andrey learned it from me, it's not obvious. They can be your end of command semicolon, replacement for it. Michael: So \gx, I looked this up, it was send the last query, send that to the server, right? Nikolay: Right, but it's expanded. So the idea is it's like semicolon, but it's expanded. So it basically, if you have very wide result set, many, many

columns, but low number of rows, for example, just 1, \gx is much better because it's equivalent to switching to expanded mode using \x. So you see 1 column on 1 line, second column on a different line, and so on. You see it transponded, right? And yeah, I just have a habit to use it when I just select something, for example, select star from users where id or email equals this.

And I want to see all columns. I just use \gx, instead of semicolon. And I don't need to deal with this formatting or horizontal scrolling if you have pspg installed. pspg I didn't mention, I do like pspg, which provides much better output and pagination in psql. Very great addition to psql. Michael: Yeah, it's pretty cool. You can even do like horizontal scrolling within the terminal.

It's pretty cool. And did you know pspg is even mentioned in the psql docs? That's pretty cool. I didn't realize that till today. Nikolay: Unfortunately, no, because it's third... I think Postgres docs don't mention third-party tools almost. Maybe except pgAdmin. I don't know if pgAdmin is mentioned in Postgres docs. Michael: I have not seen it there. But it's very rare to see a third-party tool mentioned in the

documentation. Nikolay: Maybe never. Michael: Well, this is third-party though, pspg. Nikolay: Yeah, it's third-party. Michael: So not never It is? Michael: Yeah in the docs Nikolay: It's a surprise for me Michael: Yeah, same. That's why I mean, that's why I said in psql it's mentioned under paging like it makes sense. Nikolay: That's great Nikolay: That's great. Yeah, it's it's good.

And yeah, I like it a lot. I use it all the time when I can. So yeah, also colors, it provides good colors, menu, interactive menu, so many cool stuff. Yeah, so back to \watch and \gx. \watch and \gx, semicolon is not needed, and even more, like if you use semicolon with \watch, you will get basically extra call, which may be not good.

When you start counting already With new options, new option count, it's an extra call. Right. Okay, what else? Let's talk about interactive mode and what tricks are worth knowing, In addition to \gx. So Michael: when you say interactive mode, do you mean like a user doing administrative tasks or doing some ad hoc queries?

Yeah, okay, great. Nikolay: Yeah, yeah. What are your favorite things when you rarely touch psql? Michael: I think they're not... Well, mine aren't going to be on the advanced side of things, but yeah, some of the alignment things are quite useful, so like \a can toggle off alignment if for any reason you want it. So I complained about things at the beginning. Nikolay: What's the reason to use backslash, to turn off alignment? Michael: If you want, like if you're doing some stuff with JSON,

for example, the JSON output, you don't want like a pretty nice... So it's a nice feature of psql that you get tables formatted as tables with characters but you don't want that if you're dealing with JSON. Nikolay: I always use it when I want to see the body of function or trigger function or stored procedure. So if you use \df or \sf, it was changed, right? \df plus.

I always used \df plus, but I think it got... Like this behavior was duplicated or removed and you need to use \sf or something, so you need to see function using s. And in this case, If you don't run \a before that, the formatting will skew all the indentation of the code, and it's hard to understand. But this is also my habit, \a, then \sf plus, and see the function, understand what it's doing, and so on.

Then you can even, if it's not production, you can edit the function using \ef. And you know, like this probably also gotcha. We have my mind is also, this echo from last episode. And those who didn't listen to it Like it was interesting episode to me as well So when you use \ef and I usually use VI right? Yeah, I'm a big VI fan You go to VI, you edit everything, then

you, as usual, like call on WQ, like save and exit, write and quit. And then, you know this or no? Michael: I'm not a VI user, but I've read like, \e is 1 of those tips that often comes up with Chrome. Like you can just use it to edit anything. It doesn't Nikolay: have to be a function. Right, but for specifically for functions, there is a gotcha. It's not about VI. You can, I think, use Nano or anything and still bump into this

problem? The problem is, you think it's written and saved, but Postgres prompt, not psql prompt, shows something like a transaction is not closed, right? You need the semicolon to finalize it. And this is not expected. And I'm still like many years, like I deal with it. I know this every time I deal with it, I'm thinking, why is it so? I know like there should be some explanation why it's so, but yeah. So after \ef, always semicolon.

Michael: Or \gx. Nikolay: Well, interesting. Michael: I don't know if that would work. Nikolay: Maybe. Okay, maybe. What else? I like a \set and I use it from time to time. There are 2 cases. I use it when I need some variable. Of course, this moves us already to programming mode, not interactive mode. Michael: There's overlap, right? Nikolay: Right, There's overlap for sure. I like to use it sometimes to set something to have short comment,

for example. For example, you know, we talked about buffers in explain analyze a lot and you can just define some like colon, colon, colon, colon, EAB, for example, or just colon something, I don't know. And this 1 might, yeah. Michael: In my docs for, so for getting a query plan, because we recommend explain in parentheses analyze format json buffers verbose settings while in our example we just do colon ea as

the alias for that and I but I don't personally use it I personally because I'm always working pretty much always working on the same machine, I just have a text expansion tool on Mac OS that does something similar for when I'm in interactive mode. I can understand how this becomes a little bit more. Maybe you might use this more when you're in programming mode, but people, yeah, I can see why some people would use it for interactive mode. Nikolay: Yeah, it's interesting. There's definitely overlapping between interactive and programming

mode, at least for me. Because when I think about set, I always already think about \gset, right? When the result of the output of command is used to set variables, client-side, psql variables, So you can use them later. And I do use this sometimes, for example, even in loop, even with \watch. It's interesting because, for example, you can monitor progress, you can, for example, remember previous data, including timestamp

and calculate interval. It's not always convenient and sometimes I switch to using server-side variables, which like this set SQL command, right? So \set is psql commands, set is client-side and set without backslash is just psql command, it's server-side. It's very similar to copy, by the way. Copy when you, for example, export or import data, \copy is purely client-side.

It defines where your CSV will be saved on your client machine, if it's a different machine, or on server. And permissions needed, of course, and so on. So back to \gset, you can do such stuff, like you remember what happened, then you can use it in the next query. And This already moves us to programming mode. Sometimes I remember I needed to combine client-side variables

and server-side variables. When I do server-side variables, usually I don't use set because It cannot be embedded to other queries. It's a separate utility command, basically. You cannot put it inside your SELECT or DELETE or something, or CTE. While there are 2 functions, quite weird functions, actually, because they have a second parameter and you always need to check documentation.

current_setting() and set_config(), I think. 1 is setting server side, set_config() is setting server side variable, and the second parameter, I believe, defines if it's local to transaction or to whole session. Basically global, not global for all sessions but for two-year session. And current_setting() also has second parameter, it basically reads it, right? Second parameter also required, I don't remember meaning, I always

put true, yeah, like it's, because naming is weird and that's why it's impossible to memorize it even if you have like me, like 18 years of Postgres experience, right? This is where I usually check documentation. And I wrote a lot of code, like many thousands, maybe even thousands of thousands of psql scripts. So it's so weird I cannot memorize it. Yeah. But second parameter for both these functions is what like worth

paying attention to. And also like set_config(), current_setting(), they don't look like basically doing the same thing, but in different directions, setting and getting, right? It should be something gets something said, right? Michael: I don't know. I'll include links to them in the show notes. Like I Nikolay: would prefer to have like GUC set and GUC get or something like this, right? And the good thing about server-side variables, you can define your own, and usually they always go with namespace, with prefix,

so something dot something. Because without it, it's global, there are only roughly 300 of them and this is how you control configuration of your server right so yeah and the combination of these things gives you already opportunities to start coding and since as both of us know there is also \if right we are Michael: well I didn't know I didn't know this until looking into your code so it was pretty cool Nikolay: yeah it's kind of already started starting to look like

Turing complete stuff. So you can start coding in this language, additional language on top of SQL. And I like it actually because it gives you a lot of possibilities to automate things. If-else gives you ability to, for example, handle different versions of Postgres. You can check version quickly, memorize it in a variable, maybe server-side, and then just using current config function, get

it to client-side variable, and then using if, have different pieces of your code working with different Postgres versions. For example, if you want to have something dealing with pg_stat_statements, statements, we know they change sometimes. For example, in Postgres 13, more columns were added and existing columns were changed, so it's not backward compatible. That's why if you need some report, for example, from pg_stat_statements, you need several versions of it.

This is where if for these psql scripts is super useful. Michael: Yeah, Even things like some of the really important columns like total execution time change name not Not look look recently enough. Yeah recently enough that I that I know that change But also planning time for example wasn't always in there and you probably want to add both together if you want to look at execution time plus planning time so when when that's available you probably should be summing them so yeah I can totally see that making

sense yeah well why we like I know this is super minor compared to like if else if oh sorry it's not else if it's e-l-i-f-e-l-i-f-e-l-i-f Nikolay: yeah it's if, elif, else, and or backslash and or and if backslash and if yeah this is also if you deal with multiple languages like Python, C, Java, Ruby and also this it's inevitable that you will be checking documentation when you write.

Michael: So 1 of the things that caught my eye when I was looking through the postgres_dba was you were just using echo as almost like a user interface. And 1 of them, just to show the menu of what's available, like what queries you can even run, The menus, there was some weird characters, like some weird, it's almost like, it wasn't Unicode, but it looked like kind of, they're probably... Nikolay: Emojis, you mean? Michael: No, they weren't emojis, it's like, it was color, it

was, I didn't know what it was, so I pasted the echo line into my psql and it just made the word menu pink, like bright pink. Yeah, yeah. Well, that's a nice touch. Nikolay: Right, but it's regular terminal fun. Yeah. It's not psql stuff. It's just... Michael: I know, I know. Nikolay: And it won't work, probably it won't work with some pagers, right? Some pagination stuff and in some terminals it won't work as well. So it's like unfortunately limited.

But yeah, it's for fun and I usually use it to distinguish like success from from failure like errors from Successful messages or warnings like different levels notices warnings and so on by the way Do you know why everyone is using Raise notice or Raise warning in PL/pgSQL while there is a Raise info and Raise debug. This is just lack of checking documentation maybe. Michael: Yeah probably.

Nikolay: And since we touched the \gset it's worth mentioning also there is gexec, a very powerful thing. I usually use it together with a format, so we can basically have a dynamic SQL build from like you have a select query we format you maybe by the way in some like while or something well some loop right you have this and You build some new query and it return it as a string right and then gexec just executes

it This is also quite powerful in some cases Michael: Yeah, and I've seen the example in the docs But also I've seen you do this for real when we were just testing something together once, you can use it to if you return multiple columns, it can execute multiple things all at once. So you can programmatically add 100 indexes all to the same column, I think was the example we were doing, or add the same index to 100 different tables that all have the similar naming convention,

like that kind of thing. Nikolay: Yeah, for example, if you want to create 100 indexes and don't care about naming, or maybe if you care about naming, You could just have while \watch, not while, \watch command with specify number of loops and just format it gexes and if you want to specify naming with some increasing integer number you can remember what happened before and also memorize it using server-side in this case.

Because it should be a single query, so you cannot combine G with gexec. It's better to have server-side using set_config() function, server-side variable, and then you can just add 1, plus 1, and have a different name. But in this case, I remember we didn't care about naming, I think, and I just didn't use name specification and create index command and you just get hundred indexes to check overhead I think right?

Michael: Yes that was when we were checking planning time that was the planning time episode. Nikolay: Yeah it's fun thing that we still trying to polish that experiment and make it more like you know looking good and publish blog post about this so I hope it will be ended soon finish soon and we will have this blog post published so what else what else do you want to discuss \i or what? \o, input output, right? Michael: Yeah, I've seen I used with A.

Nikolay: Yeah, \i just import something. It's good for programming mode when you want to structure your code base, psql code base. And \o is output, outputting, is moving output from your like terminal to some file, right? And I Michael: think it's the equivalent of like hyphen O when you use it from a script. And like I've used that for so query plans can get really big. If people have like several megabyte query plans, piping it to

the clipboard or sending it to a file is way, way better for like copy and pasting those around than trying to copy them from a terminal. You know, if they're thousands of lines long. Nikolay: Yeah. Right, but there's also ability to have both, right? To print it in terminal and save to file. With \o and also pipe. Vertical line. But important thing is that if there are errors, they won't go to file, right?

Unfortunately. Michael: Interesting. Nikolay: Yeah, yeah, yeah. I think so. Wait, why? Because output only successful results like Michael: a Nikolay: result set goes to not results that also system messages like number of rows this all goes goes to file because the show doesn't redirect errors, I think. Michael: Interesting. Yeah, good gotcha. You can't accuse me of always looking for gotchas and then bring them all up yourself.

Nikolay: Maybe it's just like, you know, it's not just developed or something, right? And this is also the point when I usually, again, like go to shell level again and use T Hyphen a right and Use SDR as the SDR is to the out Michael: What's T So I'm thinking T Nikolay: is like. Tee. Michael: Oh, so sorry.

Nikolay: Not British. Yeah. Michael: No, I was actually thinking of the, I've used \t to, you know, there's the beginning bit. So sometimes you get stuff around the rows. Nikolay: So like, tuples only, right? Michael: So T for tuples only, yeah, exactly. Nikolay: No, no, this T, like, when you want to see things and also to save things, if you're in programming mode, in this case, you have your script, dot psql script, you run it using psql,

you can have pipe T hyphen a, hyphen n means don't overwrite, just like append to the file, and you can also print, t also prints it, the output, and in this case not to lose errors, you can redirect errors, STD air to STD out, using, you know, like this, like ampersand and so on. And in this case, you have both errors and normal messages going both to files and to your terminal.

And this is exactly how I like to see things and not to lose them with timestamps. For example, TS from MoralTools, TS also good thing. You're prefixing everything with timestamps, so you work and you also save everything to file so if something goes wrong you can troubleshoot, analyze, to do post-mortem, root cause analysis, anything because you have all the logs of your actions. Michael: Yeah and the other way around is useful as well if you

want it in the file for like if you want the file for reasons you're getting feedback in the terminal that it's actually working, that it's actually doing what you expect. Nikolay: Yeah you see what's happening. Well some people like don't like this and say let's not leave it and no hub. Let's use no hub. So like detach it and it's running in like basically in background. And then you can observe the file using just tail hyphen F, right? Also fair. And this is how you can do a lot of quite complex coding using

psql. And this can be building blocks for very, very, very complex automation. For example, if you use Ansible, basically it's running something remotely. And sometimes, like in our case, sometimes it's a bunch of psql lines and we'd run at them remotely and they can be important pieces of such things as like 0 downtime upgrades.

The only thing I must mention always based on my mistakes from the past and my team's mistakes. When you do automation, don't forget, hyphen X, capital X. Super important. Yeah. Hyphen A is good. Hyphen T is good. Everything, but hyphen X will save you 1 day. Because if somebody left .psqlrc configuration file with timing

on, for example, all your logic comparing output to something can be super broken and can be unnoticed in tests, unfortunately. Michael: So capital X ignores psqlRC? Nikolay: Yeah, yeah. So it switches to default behavior and it's good for a programming mode. psqlRC is good for interactive mode. It's bad for a programming mode because if you have a new server and you don't have this... Well, it depends on some organization, right?

Maybe we have a rule to put some specific psqlRC everywhere. In this case, it's okay. But if you don't know what will happen in the future, it's better to just ignore those adjustments. Michael: I like doing it with X. If it's a script, it's a script already, why not set any config you want in the script and then use hyphen X like it that makes way more sense to me than relying on something else Nikolay: maybe maybe there are options here.

It's good to develop something here, not just to forget about this. This happens all the time, people, and I did it as well. We forget it, and then on staging we don't have psqlRC, on production we have, and boom, it's not working. Or even worse it's working in the wrong way right Michael: yeah good point yeah nasty Nikolay: that's why I like hyphen X hyphen capital X okay yeah there are like we maybe touched like couple of percent of what's

possible as usual right We wanted to call it advanced psql, right? But there are many more things. Michael: So we can point people out, I'll include a link to the psql docs in the show notes, and also to, there's a site by Lætitia Avrot, we mentioned last time as well, called psql tips. And if you go to it, it's a website that will just give you a random tip about psql. Every, you know, if you could set your homepage to it, or your new tab page to it, and you might learn the odd.

That's like a way of discovering new things that you might not know it can do, which is quite cool. Nikolay: Yeah, let me mention a few more things I wanted to mention. If you find yourself spending too much time inside psql like I do, you will probably like \! mark because it can run anything, right? You can, for example, run LS, PS, stop, anything you want, SSH. So it's basically running something, some shell right from psql.

And also I like the fact that I think in Postgres 16 it started to be possible to work with extended protocol and to debug some stuff from there. And also I like, I don't use it, but it's so quite powerful. You can run multiple, you can send multiple statements in 1 shot. If you, instead of semicolon use \:. Right, in this case, you can combine many different queries and

send them at once. Sometimes it's also... Michael: Are these documented? I don't remember reading about these. Nikolay: Yeah, it should be documented. Michael: That's quite cool. Nikolay: Yeah, so I don't use it often but it's quite powerful, it also shows like you can do so many things right inside psql. Michael: Yeah I just did \? which by the way is like at least 50 lines of information straight away, and

then \! is listed even in that. Nikolay: \! I use all the time, Because I just don't want to quit from psql, right? Michael: And Nikolay: we don't need to mention that quitting from psql is \q because you can right now write exit or quit Since I don't know I don't from version 14 15. I don't remember when it was added maybe earlier So this is not Michael: to be the easiest to quit command-line tool ever Nikolay: It's not VI style anymore, right?

So yeah, a lot of stuff is possible and it's good that Again, this is the only 1 single official client in Postgres project. So it's worth learning it and using it more. We mentioned in the episode about comparing it to graphical interfaces. We mentioned that terminal is good for expected automation. Like, basically for automation, you have expected behavior, you just program something and you can put it to CI-CD pipelines

or anywhere. Right? Unlike if you have some graphic interface, what else? Like, it's not good. Of course, you can use cloud computer use which was released yesterday I'm joking I'm trying to like insult you already so yeah Michael: well I don't think it's a great argument in terms of the, like, you can use a graphical tool to come up with the query you want to run, like, via the shell.

But yeah, some of the interactive, I'm saying interactive when I mean the opposite some of the stuff we've been talking about to programmatically use psql it makes sense like obviously you're going to use psql eventually so you might as well do it but I still find it some sometimes easier to play around with queries in a graphical interface, personally. I understand that. Like editing them and things. Nikolay: Yeah, I understand that, of course. If it's a huge Query and you are not using a VI by default, I

can understand this. Michael: Yeah, yeah, good point. Cool. Nikolay: Good, yeah, I like this thing and hopefully we will see development of this further and further. I mean, psql features. I feel potential to have more and more. Good, thank you. Michael: That's all, Nikolay. Thanks so much. Nikolay: Thank you.

Open the transcript file

Hosts and Guests

Hosts

  • Michael Christofides Host Feed-stated
  • Nikolay Samokhvalov Host Feed-stated

Credit the people on this episode

Be the first to rate this episode

More episodes

All episodes
  1. Postgres 19

    Nov 19, 202653 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...

    Credit the people on this episode

  2. The hidden costs of CREATE TEMP TABLE

    Nov 13, 202639 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...

    Credit the people on this episode

  3. Extensions on demand

    Nov 6, 20261 hr 9 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...

    Credit the people on this episode

  4. UUIDv4 vs UUIDv7

    Oct 30, 202648 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...

    Credit the people on this episode

  5. Dump/restore done. Now what?

    Oct 23, 202651 minAudio

    Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...

    Credit the people on this episode